VBA shell command with path containing space (1 Viewer)

Wysy

Registered User.
Local time
Today, 11:59
Joined
Jul 5, 2015
Messages
333
Hi,
I struggle to solve the problem caused by space in path in a shell command. I have read about double quoting but i can not get it working.
So this code works fine without space in path

Private Sub Command0_Click()
Dim sx As String

sx = "P:\HQHA\DB_Directory\CORs\Magic_Jasmine_COR.pdf"
Shell ("C:\Program Files\Tracker Software\PDF Viewer\PDFXCview.exe " & sx)
End Sub

However when sx string to file contain a space i can not get it worked

Private Sub Command0_Click()
Dim sx As String
sx = "P:\HQHA\DB_Directory\CORs\Magic Jasmine_COR.pdf"
Shell """C:\Program Files\Tracker Software\PDF Viewer\PDFXCview.exe""" & sx & """"
End Sub

Any advice?
thank you
 

Wysy

Registered User.
Local time
Today, 11:59
Joined
Jul 5, 2015
Messages
333
Sorry that i bothered.
Double qouted sx string and it worked...
Private Sub Command0_Click()
Dim sx As String
sx = """P:\HQHA\DB_Directory\CORs\Magic Jasmine_COR.pdf"""
Shell ("C:\Program Files\Tracker Software\PDF Viewer\PDFXCview.exe " & sx)

End Sub

thank!
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 05:59
Joined
Oct 20, 2008
Messages
512
I always find double quoting very difficult on the eyes and so easy to stuff it up, missing quotes or too many quotes. To make life easier, why not write a little function where your string to be quoted is passed in and the output is a correctly quoted string. I use the following, hope you find it of value.
Code:
Function satEnclose(Optional pStr As Variant = "", Optional pIntType As Integer = 2) As String
    
    ' use this instead of directly writing " " "  or " ' "  or " # " in SQL statements, eases confusion
    ' It will make writing SQL code easier, easier code to view.
    ' Author:   Steven Taylor aka essaytee
    ' Date:     23 Feb 2019
    '
    ' INPUTS    pStr        The string value to be enclosed     (default value is an empty string)
    '           pIntType    0 = Not enclosed
    '                       1 = Single quote '
    '                       2 = Double quote "                  (default value if not passed in)
    '                       3 = Pound/Hash #
    '                       4 = Less/Greater than < >
    '                       5 = Square brackets [ ]
    '                       6 = Curly braces { }
    '                       7 = Parenthesis ( )
    '
    ' OUTPUT    The passed in string (pStr) enclosed in quotes or hash or <> []
    '
    '
    ' NOTES     This simple function could be further developed to account for other enclosing symbols
    '           To find the Chr$ number of symbol, Immediate window eg. ? Asc("<") will give you the number
    '
    ' Sample sql
    ' Dim strSql as string
    ' strSql = "SELECT * FROM tblWhatever WHERE FldName = " & satEnclose(Me.txtSearchName) & " FldAddress = " & satEnclose(Me.txtSearchAddress) & ";"
    ' As written in line above, the string values will be returned enclosed in double quotes.
    '
    ' Optional:     Why does pStr default to an empty string.  Legacy issue for me, I did have functions just returning " or ' or #
    ' Lastly:       In relation to dates I've created another similar function that accounts for the US format, required for SQL, and encloses in #
    '
    '
    
    Dim strEncloseLeft As String
    Dim strEncloseRight As String
            
    Select Case pIntType
        Case 1 ' Single quote - think 1 as single quote
            strEncloseLeft = Chr$(39)
            strEncloseRight = Chr$(39)
        Case 2 ' Double quote - think 2 as two quotes, therefore double quote
            strEncloseLeft = Chr$(34)
            strEncloseRight = Chr$(34)
        Case 3 ' Pound/Hash #
            strEncloseLeft = Chr$(35)
            strEncloseRight = Chr$(35)
        Case 4 ' Less than, greater than   < >
            strEncloseLeft = Chr$(60)
            strEncloseRight = Chr$(62)
        Case 5 ' Square brackets [ ]
            strEncloseLeft = Chr$(91)
            strEncloseRight = Chr$(93)
        Case 6 ' Curly braces { }
            strEncloseLeft = Chr$(123)
            strEncloseRight = Chr$(125)
        Case 7 ' parenthesis ( )
            strEncloseLeft = Chr$(40)
            strEncloseRight = Chr$(41)
    End Select
        
    If pStr = "" Then
        satEnclose = strEncloseLeft
    Else
        satEnclose = strEncloseLeft & pStr & strEncloseRight
    End If

End Function

For your example:
Code:
sx = satEnclose("P:\HQHA\DB_Directory\CORs\Magic Jasmine_COR.pdf")
Shell ("C:\Program Files\Tracker Software\PDF Viewer\PDFXCview.exe " & sx)
 

Users who are viewing this thread

Top Bottom