TryCode:Shell Replace(fPath, " ", "%20") & "test.bat"
I have a problem with paths and filenames with spaces.
If the the excel file containing the macro file is opened from explorer rather than from within excel, the path is incorrect. Therefore I use the following code:
I can then later call either varPath or the VBA GetCurDir and it will be correctly set.Code:Dim varPath As String varPath = ThisWorkbook.Path ChDir varPath
However, when I call a Shell function as in the example:
I get the message box: e:\local data tables\temp\test.batCode:Dim varPath As String Dim fPath As String varPath = ThisWorkbook.Path ChDir varPath fPath = varPath & "\temp\" MsgBox fPath & "test.bat" Shell fPath & "test.bat"
I get the error: Runtime Error 5, Invalid procedure call or argument.
Without having to rename the path structure so that it doesn't have spaces (which isn't always possible), how can I get this to work?
Maybe i've left a step out....
Cheers in advance,
Seems to be one step closer, but now i'm getting runtime error 76, Path not Found. I've msgbox'd the string and it is doing exactly what you're suggesting by replacing the spaces with "%20" but it doesn't seem to want to accept them as spaces.
e:\local%20data%20tables\temp\test.bat is what i'm getting. The slashes are around the right way aren't they?
Yes on the slashes question. Its ok.
Last edited by fengore; May 9th, 2006 at 15:13.
Eternity is a terrible thought. I mean, where's it going to end??
Yeah, something screwy is going on...
It's strange how i get a Invalid procedure call or argument using actual spaces, but i get a Path not Found error when the path is all real characters.
In my real application, the path is actually fairly long.. .as in 77 characters from drive letter to the end of ".bat". Would that be causing a problem?
The other option i thought of was...rather than relplacing the spaces with %20, could i add inverted commas "" to the front and back of the filename string? Instead of the VBA Replace command, is there and Add or Prefix/Suffix command?
Problem solved. I found that the Shell command is a bit different to the rest in that it does not handle spaces or read weird characters very well.
What it needs is the following code (based on my original code at the start of the thread):
It's the """" that puts the string in inverted commas. Therefore it can read any file or path with spaces in it.... My Documents, Documents and Settings, Program Files etc....Code:Dim varPath As String Dim fPath As String varPath = ThisWorkbook.Path fPath = varPath & "\temp\" Shell """" & fPath & "test.bat" & """"
I think in my crazy afternoon, i also found that some system variables have the directory slashes in one direction, while others in the other direction. So to combat this problem i've also updated the code so I don't need to worry:
Thanks for the idea, Dave.Code:Dim varPath As String Dim fPath As String varPath = ThisWorkbook.Path fpath = Replace(varPath, "\", "/") & "/Temp/" Shell """" & fPath & "test.bat" & """"
There are currently 1 users browsing this thread. (0 members and 1 guests)