CODEWITHSUNDEEP
excelshellbatch-file
user1939275
user1939275

Reputation: 9

How do I pass multiple variables from an Excel file to a batch file

I am currently able to pass one argument from my Excel file to my batch file using the following: 

 filepath = "C:\Users\agaron\Desktop\batchmaster\batchfiles\batchfiletest.bat " & month

followed by calling a shell command:

    Call Shell(filepath, vbNormalFocus)

This works fine. However, I now require to pass not just one, but 18 parameters using the above method. I tried concatenating using the & but it appears that the batch file recognizes all the parameters as a single one.

How can I pass more parameters from Excel to batch file?

Upvotes: 0

Views: 1906

Answers (2)

aschipfl
aschipfl

Reputation: 34909

To provide multiple parameters to a batch file, separate them with spaces. If a parameter contains spaces on its own, enclose it in "" (this is also required for some other characters like ,, ;, =; use quotes also for the batch file path in case; the quotes might also be used for every parameter):

"C:\Users\agaron\Desktop\batchmaster\batchfiles\batchfiletest.bat" param1 param2 "param3 with spaces" ...

To access the parameters in the batch file, use the %# syntax, were # represents a single decimal digit (see call /? for details). Note that %0 returns the path of the batch file itself. To remove potential surrounding "", use %~#:

echo This batch file: %~0
echo First  parameter: %~1
echo Second parameter: %~2
echo Third  parameter: %~3

With the shift command, you shift the assignment of the %# numbers, for instance:

echo This batch file: %~0
shift
echo First  parameter: %~0
echo Second parameter: %~1
shift
echo Third  parameter: %~1

As you might have noticed, you cannot access the batch file path anymore after the first shift; the next shifts prevents the first parameter from being accessed, and so on (type shift /? for more information).

However, shift lets you access more than 9 parameters. For example, when calling the batch file with a command line like:

"C:\Users\agaron\Desktop\batchmaster\batchfiles\batchfiletest.bat" param1 param2 param3 ... param9 param10

you can access the parameters like:

echo This batch file: %~0
echo Parameter  1: %~1
shift
echo Parameter  2: %~1
shift
echo Parameter  3: %~1
:: ...
shift
echo Parameter  9: %~1
shift
echo Parameter 10: %~1

As you can see, shift provides the possibility to access even more then 9 parameters.

Finally, let us call the batch file via the VBA code:

filepath = Chr(&H22) & "C:\Users\agaron\Desktop\batchmaster\batchfiles\batchfiletest.bat" & Chr(&H22) _
    & " " & variable_containing_param1 _
    & " " & "param2" _
    & " " & Chr(&H22) & "param3 with spaces" & Chr(&H22)
Call Shell(filepath, vbNormalFocus)

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61880

Windows batch files get different parameters delimited by spaces. So if your batch file batchfiletest.bat is like:

echo off
echo %1
echo %2
echo %3
pause

then the following VBA should run properly:

Sub testBatch()
 sMonth = Format(Now, "mmmm")
 sDay = Format(Now, "dd")
 sYear = Format(Now, "yyyy")
 filepath = "C:\Users\axel\batchfiletest.bat " & sMonth & " " & sDay & " " & sYear
 Shell filepath, vbNormalFocus
End Sub

Upvotes: 1

Related Questions