Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Spaces in path and filename

  1. #1
    Join Date
    2nd May 2006
    Posts
    17

    Spaces in path and filename

    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:

    VB:
    Dim varPath As String 
    varPath = ThisWorkbook.Path 
    ChDir varPath 
    
    
    I can then later call either varPath or the VBA GetCurDir and it will be correctly set.

    However, when I call a Shell function as in the example:

    VB:
    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 message box: e:\local data tables\temp\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,
    Daniel

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,705

    Re: Spaces in path and filename

    Hi Daniel

    Try
    VB:
    Shell Replace(fPath, " ", "%20") & "test.bat" 
    
    

  3. #3
    Join Date
    2nd May 2006
    Posts
    17

    Re: Spaces in path and filename

    Thanks Dave,

    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?


    DAniel

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th March 2005
    Location
    Los Angeles
    Posts
    500

    Re: Spaces in path and filename

    Yes on the slashes question. Its ok.
    Last edited by fengore; May 9th, 2006 at 14:13.
    Eternity is a terrible thought. I mean, where's it going to end??

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,705

  6. #6
    Join Date
    2nd May 2006
    Posts
    17

    Re: Spaces in path and filename

    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?

    Daniel

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    2nd May 2006
    Posts
    17

    Re: Spaces in path and filename

    Hi Guys,

    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):

    VB:
    Dim varPath As String 
    Dim fPath As String 
    varPath = ThisWorkbook.Path 
    fPath = varPath & "\temp\" 
    Shell """" & fPath & "test.bat" & """" 
    
    
    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....

    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:

    VB:
    Dim varPath As String 
    Dim fPath As String 
    varPath = ThisWorkbook.Path 
    fpath = Replace(varPath, "\", "/") & "/Temp/" 
    Shell """" & fPath & "test.bat" & """" 
    
    
    Thanks for the idea, Dave.

    Regards,
    Daniel

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Extract filename from a path
    By Obelix in forum EXCEL HELP
    Replies: 7
    Last Post: February 28th, 2006, 20:36
  2. Removing path from filename
    By squeegedog in forum EXCEL HELP
    Replies: 1
    Last Post: October 25th, 2005, 12:55
  3. isolating the filename from the path?
    By Turbothorup in forum EXCEL HELP
    Replies: 5
    Last Post: August 3rd, 2004, 00:46
  4. Filename & Path on Custom Footer
    By rdobney in forum EXCEL HELP
    Replies: 2
    Last Post: May 7th, 2003, 23:41
  5. Getting Filename from full file path
    By the_sleeper in forum EXCEL HELP
    Replies: 1
    Last Post: February 26th, 2003, 09:29

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno