How do I correctly format the Shell 'path' string in VBA to open notepad with a file at-CodePudding

I have tried a number of different ways to construct the string to call notepad with a filename to no avail. I need to also utilize the '-n' parameter for notepad to open a file at a particular line. Simple cases work, however when concatenating strings for the path I have been getting runtime 424 errors. This is in VBA and is a macros within Excel.

Option Explicit

Sub GoToLine()
 
    Dim strNotePadPath As String
    strNotePadPath = "C:\Program Files\NotePad  \notepad  .exe "

    Dim strSourceBasePath As String
    strSourceBasePath = "C:\VBAExcelTest\TestSource"
    
    Dim strSourcePathFinal As String
    strSourcePathFinal = strSourceBasePath & Cells(Selection.Row, 1).Value
    
    Dim strLineNumber As String
    strLineNumber = " -n" & Cells(Selection.Row, 2).Value
    
    Dim retval As Variant
    'This works:    retval = Shell("C:\Program Files\NotePad  \notepad  .exe C:\VBAExcelTest\TestSource\SourceA\FakeSourceA.txt -n1", 1)
    'I get a runtime error 424 on the Call Shell line below
    If Selection.Row.Count = 1 Then
        Call Shell("""" & strNotePadPath & strSourcePathFinal & strLineNumber & """", vbNormalFocus)
    End If
    
End Sub

CodePudding user response：

Sometimes a token-replacement approach is easier to manage when dealing with escaped quotes etc:

Sub GoToLine()
 
    Dim cmd As String, retval As Variant, rw As Range
    
    Set rw = Selection.Cells(1).EntireRow
    
    cmd = Tokens("""{1}"" ""{2}"" {3}", _
                 "C:\Program Files\NotePad  \notepad  .exe", _
                 "C:\Tester\tmp\" & rw.Cells(1).Value, _
                 "-n" & rw.Cells(2).Value)
    
    Debug.Print cmd
    
    Shell cmd, vbNormalFocus

End Sub

'replace tokens in the first argument, using the rest of the arguments
Function Tokens(txt As String, ParamArray args() As Variant) As String
    Dim i As Long, t As Long
    t = 0
    For i = 0 To UBound(args)
        t = t   1
        txt = Replace(txt, "{" & t & "}", args(i))
    Next i
    Tokens = txt
End Function