Log In
Register
Home
October 17, 2018
Offline
Hi this is a general question about 'sudden' issues some users are running into after the latest Office 365 update. The reaso I know it's due to this is because one of the people I wrote an Excel application was using it yesterday without problems and her PC was updated early this morning and it no longer works.
It happens when the shell command is run that I use to list a number of files to a temporary file written to the user's temp folder:
[code]
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)
[code/]
You can rest assured that the variables are all correct and when I run it directly from the command prompt no problem but in the VBA code if fails
The first line KILL works, the file is removed, but the Call Shell line throws the error
The Error message is Error 5 invalid procedure or invalid argument (freely translated from the Dutch message box
Apart from that, but of course not everything can be tested now it seems it runs Okay,
Have any of you encountered problems since let's say a week or two?
I asked their network engineer to look at it and he noticed that the Office updates started March 29 and it was just her system that was updated this morning
I'me very curious and worried, I did not read anything on any blog or site about Office 365 modifications to the good old MS-DOS commands
Will need to write an alternative code for it
October 5, 2010
Offline
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
October 17, 2018
Offline
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:
[code]
'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:\HOUTROT\Datalogs\*-houtrot-dataverwerking-projectlogs.xlsx" /B > C:\Users\keebe\AppData\Local\Temp\tempfilelist.lst
[/code]
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.
October 17, 2018
Offline
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
[code]
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
[/code]
October 5, 2010
Offline
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
October 17, 2018
Offline
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
October 5, 2010
Offline
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
1 Guest(s)
