Passing parameters stored in a variable to bat file from excel macro

user_9454131 0
Viewed 76

I have a scenario where two arguments which may contain white space need to be passed to a bat file during runtime via macro script.

bat file script:

echo %1
echo %2

Vba:

Sub Button1_Click()
    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")    
    Dim oExec As Object
    Dim oOutput As Object
    Dim arg1 As String: arg1 = "New Folder"
    Dim arg2 As String: arg2 = "My Activity"
    Set oExec = oShell.Exec("G:\Sample\test.bat" & " " & arg1 & " " & arg2)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then MsgBox sLine
    Wend
End Sub

The above code gives output as follows:

New
Folder

but expected output is:

New Folder
My Activity

Kindly help me in resolving this issue as am new to macro

vba excel
2 Answers

Arguments which may have spaces should be quoted

Set oExec = oShell.Exec("G:\Sample\test.bat" & " """ & arg1 & """ """ & arg2 & """")
user_4788840

What about simply using a temporary placeholder as WScript doesn't seem to accept non-breaking space (Chr(160)) instead of simple space?

Sub EchoBatTest()
    Const tmp As String = "$!$"                        ' << define any temporary placeholder
    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")
    Dim oExec As Object
    Dim oOutput As Object
    Dim arg1 As String: arg1 = "New" & tmp & "Folder"  ' << insert tmp
    Dim arg2 As String: arg2 = "My" & tmp & "Activity"
    Set oExec = oShell.Exec("C:\Users\Admin\Documents\Stack\txt\EchoTest.bat" & " " & arg1 & " " & arg2)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = Replace(oOutput.ReadLine, tmp, " ")    ' << re-insert space(s)
        If sLine <> "" Then MsgBox sLine
    Wend
End Sub
user_64602970
Related