Hi Hans,

I couldn't execute your code as-is because it's not complete - I don't have the variables you are using set to any values.

But if I just try Call Shell("cmd /C dir ") it works fine.  I tried this on Excel 2013 and in 365.

If you try to execute just Call Shell("cmd /C dir ") does that work for you?

Regards

Phil

Hi Phil, 

Thanks for your answer
I understand you don't have the variables, but that's not the issue, the fact is that under Office 365 Business the code no longer works.

This is what the full string looks like:

'the line in the VBA code

Call Shell("cmd /C dir """ & Replace(tPath & "" & tName, "\", "") & """ /B > " & Environ$("TEMP") & "tempfilelist.lst", vbMinimizedNoFocus)

the actual string executed:
cmd /C dir "S:HOUTROTDatalogs*-houtrot-dataverwerking-projectlogs.xlsx" /B > C:UserskeebeAppDataLocalTemptempfilelist.lst

I know how this works and I've been using this module since 2010 and the person using my application had no problems on Monday (April 1st and no Fool's day)

The Office Update on her system took place Tuesday morning early and when she wanted to work with the file ran  into this issue. I also posted on the Microsoft site but 'til now silence.

I wrote an alternative code for this avoiding the use of the file created in the temp folder and happliy it works but now I just want to know if thsi a goof-up by Microsoft or is it actually another functionality which is no longer use able as other several years ago that 'suddenly' where no longer available.

This is the full code.

GetProjectlogBestanden is the modified version that I rewrote so that it works

GetProjectlogBestanden2 is the original code that fails with Office 365 Business

Public Function GetProjectlogBestanden(tPath As String, tName As Variant) As Variant
Application.StatusBar = "Even geduld aub ..."
Dim wsM As Worksheet
Dim dtDelay As Date
dtDelay = Now
Dim File As Integer
Dim isPresent As Boolean
Dim rng As Range
Dim TextOfLine As String, myArr()
Dim vFile As String
Dim i As Integer
tPath = TrailingSlash(tPath)
vFile = Dir(tPath & tName)
i = 0
If vFile <> "" Then
Do
TextOfLine = Left(vFile, InStr(1, vFile, "-Houtrot") - 1)
If LCase(TextOfLine) <> "sjabloon" Then
i = i + 1
ReDim Preserve myArr(1 To i)
myArr(i) = TextOfLine
End If
vFile = Dir()
Loop While vFile <> ""
End If
Application.StatusBar = Captiontxt
If i = 0 Then i = i + 1: ReDim myArr(1 To i): myArr(i) = "geen Project Logbestanden aanwezig"
GetProjectlogBestanden = IIf(i <> 0, myArr, vbNullString)
End Function

Public Function GetProjectlogBestanden2(tPath As String, tName As Variant) As Variant
Application.StatusBar = "Even geduld aub ..."
Dim wsM As Worksheet
Dim dtDelay As Date
dtDelay = Now
Dim File As Integer
Dim isPresent As Boolean
Dim rng As Range
Dim TextOfLine As String, myArr()
Dim vFile As String
Dim i As Integer
If FileExists(Environ$("TEMP") & "tempfilelist.lst") = True Then Kill Environ$("TEMP") & "tempfilelist.lst"
Call Shell("cmd /C dir """ & Replace(tPath & "" & tName, "\", "") & """ /B > " & Environ$("TEMP") & "tempfilelist.lst", vbMinimizedNoFocus)
WaitForIt:
Application.Wait dtDelay + TimeSerial(0, 0, 2)
If FileExists(Environ$("TEMP") & "tempfilelist.lst") = False Then GoTo WaitForIt

File = FreeFile
Open Environ$("TEMP") & "tempfilelist.lst" For Input As File
i = 0
While Not EOF(File)
Line Input #File, TextOfLine
TextOfLine = Left(TextOfLine, InStr(1, TextOfLine, "-Houtrot") - 1)
If LCase(TextOfLine) <> "sjabloon" Then
i = i + 1
ReDim Preserve myArr(1 To i)
myArr(i) = TextOfLine
End If
Wend
Close File
Kill Environ$("TEMP") & "tempfilelist.lst"
Application.StatusBar = Captiontxt
If i = 0 Then i = i + 1: ReDim myArr(1 To i): myArr(i) = "geen Project Logbestanden aanwezig"
GetProjectlogBestanden2 = IIf(i <> 0, myArr, vbNullString)
End Function

Hi Hans,

Neither of these functions work as-is because Captiontxt is not defined, neither is the FileExists function.  If you could supply the actual workbook with data, sample function calls and exactly what you are expecting as an outcome it'll makes things a lot easier for me.

It looks like you are testing if the file tempfilelist.lst exists, and if it does then you are deleting it.

The Shell call is then doing a dir listing and output is redirected into tempfilelist.lst

As you are using a single > for redirecting the dir command output, the contents of tempfilelist.lst are overwritten, you don't need to delete it before hand.

If you were using >> to redirect output, the file contents are appended.

I'm guessing at exactly what you are trying to do but it looks like you are listing a file type specified by tName (e.g. *.log) and then trying to read the dir listing of all those files from the tempfilelist.lst

I changed both functions to subs for testing and the Shell call did start a command prompt in Excel 2013, but the dir didn't seem to work.

The rewritten function using Dir() is a better solution and worked for me so I'd stick with that rather than spending more time debugging the old Shell version.

Regards

Phil

Forgot about the Function Captiontxt, sorry, that's nothing, the TempFileList.lst is the files name and extension the log is part of the xlsx filename.

Yess I know the difference between the > and >> but I need the > and to make sure I kill the file if it exists before anyway.

What bugs me is the Microsoft either doesn't  have enough testers or just drop bombs and don't notify when updates come out, probably hidden somewhere deep in small type, but still.

It's awfully quiet on the Microsoft forum, no replies or anything on my question.

Thanks for looking anyway.

Cheers,

Hans

Hi Hans,

Unfortunately things like this do occur.  Only yesterday I had to rewrite code that had worked previously for embedding workbooks in a website.  Because MS had changed the underlying way they allowed you to embed workbooks 🙁

Phil