Consulting

Results 1 to 8 of 8

Thread: Solved: Punctuation in Folder names

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location

    Solved: Punctuation in Folder names

    It's a losing battle requesting users not to use punctuation in folder names, so my code to open the folders using Explorer is failing.
    Here's a typical example where Pth is usually created from 3 separate fields. I get the error: "The path '2' does not exist or is not a directory"
    Any suggestions?

    [vba]
    Sub test()
    'Pth = "C:\Aval\" & Me.Town & "\" & Me.Street & "\" & Me.NameNo
    Pth = "C:\Aval\Birnam\The Avenue\Bonaccord, 2"
    Shell Environ("windir") & "\Explorer.exe " & Pth, vbMaximizedFocus
    End Sub
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  2. #2
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,285
    Location
    How does the user creates a directory with a , in it ? I can't do it by using the manual way. When using code I generally run a script to remove all the illegal characters and create the folder or do a check when inputting their adress. I've got problems with users using a . in filenames. Automatic opening by right checking of extension fails if they used a . somewhere in the name. The program that needs to open the file can't do it because it doesn't recognise the extension.

    Charlize

  3. #3
    The explorer command line is looking for the old dos style file names. You could write a routine to convert the path to the Dos Style name, but that particular function is already prebuilt in the Microsoft Scripting RunTime. (See Below)
    [VBA]Option Explicit
    #Const ccReferenceSet = False
    Sub Test()
    #If ccReferenceSet Then
    'Requires referene to microsoft Scripting Runtime
    Dim oFSO As Scripting.FileSystemObject
    Set oFSO = New Scripting.FileSystemObject
    #Else
    Dim oFSO As Object
    Set oFSO = VBA.CreateObject("Scripting.FileSystemObject")
    #End If
    Dim sPth As String
    sPth = "C:\My Test. YourTest too\"
    sPth = oFSO.GetFolder(sPth).ShortPath
    VBA.Shell VBA.Environ$("windir") & "\Explorer.exe " & sPth, vbMaximizedFocus
    End Sub[/VBA]

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Thanks Oorang,
    I've converted it into a simple function that I can apply to a few projects.
    Regards
    Malcolm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Charlize,
    A comma is not listed in the 9 illegal characters. The text for the folder name is also used in letter headings etc, so I can't always remove punctuation.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    I get the error: "The path '2' does not exist or is not a directory"

    My experience is that the space is your problem, not the comma. That is, the space between the comma and your 2. As a rule, when shelling, I always enclose my path arguments in quotes rather there are spaces or not.

    pth = Chr(34) & "C:\Aval\Birnam\The Avenue\Bonaccord, 2" & Chr(34)

  7. #7
    Hi Tom,
    Shell looks for Dos Style names so if you want to be technical the problem is both the comma and the space. You can read more about it here: http://www.computerhope.com/issues/ch000209.htm

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Tom,
    I don't find the problem occuring with spaces only. In this example "The Avenue" does not cause an error. Regarding enclosure in quotes, the names are always compiled from merge fields or userforms and passed as variables.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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