Consulting

Results 1 to 10 of 10

Thread: Open Folder with Comma and Ampersand in Name

  1. #1

    Open Folder with Comma and Ampersand in Name

    Open Folder with "," and "&" in name:

    We have some folders on our network that contain commas and ampersands in their names. Unfortunately many people access these folders with hyperlinks which make removing of the commas and ampersands a bit awkward.

    I use shortcuts in my QAT to open folders I use often, but can't get the code to work when the folder name contains commas and ampersands.

    I use, for example, the following code to open a folder called Work in Progress - Piet's Folder
    Sub PSM_Working_Directory() 
         
        Dim strRootPath As String 
         
        Const strExpExe = "explorer.exe" 
        Const strArg = " " '" /e,/root, "
         
        strRootPath = "\\mgadmdat\public\Process Safety\Work in Progress - Piet's Folder" 
         
         
        PID = Shell(strExpExe & strArg & strRootPath, 3) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    The problem comes in when I try to open a folder named Offsites, B & S


    Sub Blending_and_Storage_Directory()Dim strRootPath As String 
         
        Const strExpExe = "explorer.exe" 
        Const strArg = " " '" /e,/root, "
         
        strRootPath = "G:\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S" 
         
         
        PID = Shell(strExpExe & strArg & strRootPath, 3) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    Any help would be greatly appreciated.

    Regards,
    van hunk

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    Const Coma As String = Chr(44) 'Ascii Comma
    Const Amp As String = Chr(38) 'Ascii Ampersand
     
    strRootPath = "G:\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites" & Coma & " B " & Amp & " S" 
    
    
    Formatting tags added by mark007
    This is also handy when trying to use Quotes and Double Quotes
    Const SQ As String = Chr(39) 'Ascii Single Quote
    Const DQ As String = Chr(34) 'Ascii Double Quote
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  3. #3
    Or ?

    Sub M_snb() 
        Shell "explorer ""G:\OF\Offsites, B & S""", 3 
    End Sub 
    
    
    Formatting tags added by mark007

  4. #4
    I am not sure that , or & is the problem. Space characters are usually the problem but the embedded quote method that snb showed and that below should suffice. Howsoever, you need to use Replace() to convert characters like space characters and others if you use them in a hyperlink.

    Sub Blending_and_Storage_Directory() 
        Dim PID As Integer, strRootPath$ 
        strRootPath = "G:\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S" 
         'strRootPath = ThisWorkbook.Path
        PID = Shell("explorer " & """" & strRootPath & """", vbMaximizedFocus) 
    End Sub 
    
    
    Formatting tags added by mark007

  5. #5
    Thank you very much for the excellent feedback, I appreciate your time and effort.

    @SamT
    I could not get your suggestion to work

    @snb
    Works perfect, as always.
    Sub M_snb() 
        Shell "explorer ""\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S""", 3 
    End Sub 
    
    
    Formatting tags added by mark007
    OR long way:
    Sub Blending_and_Storage_Directory() 
         
        Dim strRootPath As String 
         
        Const strExpExe = "explorer.exe" 
        Const strArg = " " 
         
        strRootPath = """\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S""" 
         
        PID = Shell(strExpExe & strArg & strRootPath, 3) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    @ Kenneth Hobs
    It doesn't have a problem with the spaces, the other folder names also contain spaces and work without extra quotes. The hyperlinks also have no problem with it. Or maybe I understand you wrong.

    Your code also works perfect:
    Sub Blending_and_Storage_DirectoryKH() 
        Dim PID As Integer, strRootPath$ 
         
        strRootPath = "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S" 
         'strRootPath = ThisWorkbook.Path
         
        PID = Shell("explorer " & """" & strRootPath & """", vbMaximizedFocus) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    Regards,
    van hunk

  6. #6
    @SamT

    I get an error message in the Const Coma As String line, stating "Constant expression required"

    Sub Blending_and_Storage_DirectoryST() 
         
        Dim strRootPath As String 
         
        Const strExpExe = "explorer.exe" 
        Const strArg = " " 
        Const Coma As String = Chr(44) 'Ascii Comma
        Const Amp As String = Chr(38) 'Ascii Ampersand
         
        strRootPath = "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites" & Coma & " B " & Amp & " S" 
         
        PID = Shell(strExpExe & strArg & strRootPath, 3) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    Regards,
    van hunk
    Last edited by vanhunk; 01-11-2018 at 02:19 AM. Reason: typo

  7. #7
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    "Constant expression required" 
    
    
    Formatting tags added by mark007
    Eh?

    Try using = "," and = "&" and = "'" and = """, but the ="&" may give a problem. I've never had to use a Ampersand in a Name or variable.

    This will work for sure, but it is longer
    Dim Amp As String 
    Amp = Chr(38) 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  8. #8
    @SamT
    Thank you Sam, this is becoming a bit academic but I still found it interesting to see what works and what not. I have used your suggestions and those of others and came up with a list of expressions for that works (obviously it could still all be "simplified" into a one liner as proposed by snb):

    Option Explicit 
     
    Sub Blending_and_Storage_DirectoryST() 
         
        Dim strRootPath As String 
        Dim PID As Integer 
         
        Dim Amp As String 'Only required if Amp is used in strRootPath
        Amp = Chr(38) 'Only required if Amp is used in strRootPath
         
        Const strExpExe = "explorer.exe" 
        Const strArg = " " 
         
        Dim Opt As Integer 
        Opt = InputBox("Select Option 1 to 6", "strRootPath Formula used") 
         
        Select Case Opt 
             
        Case Is = 1 
            strRootPath = """" & "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites" & "," & " B " & Amp & " S" & """" 
             
        Case Is = 2 
            strRootPath = """" & "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites" & "," & " B " & "&" & " S" & """" 
             
        Case Is = 3 
            strRootPath = """" & "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites," & " B " & "&" & " S" & """" 
             
        Case Is = 4 
            strRootPath = """" & "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B " & "&" & " S" & """" 
             
        Case Is = 5 
            strRootPath = """" & "\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B " & "& S" & """" 
             
        Case Is = 6 
            strRootPath = """\\mgadmdat\public\Technology Support\Process Technology\ARCHIVE 2\Proc Tech\Offsites, B & S""" 
             
        End Select 
         
        PID = Shell(strExpExe & strArg & strRootPath, 3) 
         
    End Sub 
    
    
    Formatting tags added by mark007
    Regards,
    van hunk

  9. #9
    Did you know ?

    Sub M_snb() 
        Select Case InputBox("Select Option 1 to 6", "strRootPath Formula used") 
        Case "1" 
             
        Case "2" 
             
        End Select 
    End Sub 
    
    
    Formatting tags added by mark007

  10. #10
    Quote Originally Posted by snb View Post
    Did you know ?

    Sub M_snb() 
        Select Case InputBox("Select Option 1 to 6", "strRootPath Formula used") 
        Case "1" 
             
        Case "2" 
             
        End Select 
    End Sub 
    
    
    Formatting tags added by mark007
    Thank you Sir, appreciated!
    vanhunk

Tags for this Thread

Posting Permissions

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