View Full Version : [SOLVED:] Open Folder with Comma and Ampersand in Name
vanhunk
01-10-2018, 06:39 AM
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
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
Or ?
Sub M_snb()
Shell "explorer ""G:\OF\Offsites, B & S""", 3
End Sub
Kenneth Hobs
01-10-2018, 10:40 AM
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
01-11-2018, 02:08 AM
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
01-11-2018, 02:18 AM
@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
"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
01-12-2018, 01:24 AM
@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
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
01-12-2018, 03:06 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.