CODEWITHSUNDEEP
vbaexcelbatch-filecmd
Nicolas Klebusits
Nicolas Klebusits

Reputation: 55

Passing a variable from vba to .bat file

I'm trying to pass a variable from word VBA to a batch file. The variable is an input box that the user has to type their username into. The batch file is then supposed to take that username and check it against an AD group in our directory. I must present the variable in the VBA first as I need to manipulate it in the VBA script after the batch file runs (unless it would be easier to pass a batch variable into word vba). All of the research I've done leads me to something along these lines:

Dim val, r As String
r = InputBox("Please enter your username:")
val = "path\mybat.bat" & r
Call Shell(val, vbNormalFocus)

or:

Dim val, r As String
r = InputBox("Please enter your username:")
val = "path\mybat.bat r"
Call Shell(val, vbNormalFocus)

I keep getting a run-time error 53 in VBA. I'm also not sure how to call the variable once it's passed into the .bat file.

Upvotes: 1

Views: 1519

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

val = "path\mybat.bat r"

r is just another character in the string literal "path\mybat.bat r" here - how is VBA supposed to know that you meant that r to refer to that local r variable?

val = "path\mybat.bat " & r 'note the trailing space in the string literal

Use it as a variable: pull it out of the string literal, concatenate it.

Error 53 is "File not Found". By forgetting the trailing space you're invoking mybat.batAndWhateverRcontains, which likely isn't an existing file name. Hence, "file not found".

Also note this common trap:

Dim val, r As String

That declares r as a String, and leaves val as an implicit Variant.

Dim val As String, r As String

Declares both as String variables.

Upvotes: 1

Related Questions