Open Folder with Comma and Ampersand in Name

[vanhunk]

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

[SamT]

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

[snb]

Or ?


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

[Kenneth Hobs]

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

[vanhunk]

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

[vanhunk]

@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

[SamT]

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

[vanhunk]

@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

[snb]

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

[vanhunk]

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