Consulting

Results 1 to 10 of 10

Thread: Open Folder with Comma and Ampersand in Name

  1. #1
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    240
    Location

    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
    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
    Any help would be greatly appreciated.

    Regards,
    van hunk

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    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"
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Or ?

    Sub M_snb()
        Shell "explorer ""G:\OF\Offsites, B & S""", 3
    End Sub

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  5. #5
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    240
    Location
    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
    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
    @ 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
    Regards,
    van hunk

  6. #6
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    240
    Location
    @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
    Regards,
    van hunk
    Last edited by vanhunk; 01-11-2018 at 02:19 AM. Reason: typo

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    "Constant expression required"
    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)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    240
    Location
    @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
    Regards,
    van hunk

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  10. #10
    VBAX Tutor
    Joined
    Jun 2012
    Posts
    240
    Location
    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
    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
  •