CODEWITHSUNDEEP
excelms-accessbatch-filevba
user12059
user12059

Reputation: 733

Pass variable value to batch file

Can't pass the variable value to a batch file:

VBA code executed when clicked on an Access 2007 form button.

Private Sub OpenWB(var1 As String)
    Dim RetVal
    RetVal = Shell("S:\WildlifeHealth\Idaho\Incoming\test.bat var1", vbNormalFocus)
End Sub

Code from sub routine that calls OpenWB:

idahofile = "S:\WildlifeHealth\Idaho\Incoming\test.xls”
Call OpenWB(idahofile)

test.bat file:

Call %1

Returns “var1” instead of "S:\WildlifeHealth\Idaho\Incoming\test.xls”

If test.bat file:

Call S:\WildlifeHealth\Idaho\Incoming\test.xls, file opens.

So how can I pass the value of var1 instead of the name var1.

I am trying to find a work around to Access keeping a process of excel running in the process window after the user closes excel. It is not closed programmatically through VBA. I thought if I could pass the pathname that was selected in a dialog box in Access to a batch file and open it there (hence not from Access but the command line), my problem would be solved. But alas, I can’t seem to pass the variable value.

Ideas anyone?

Perhaps there is another programming language that can handle this issue?

Upvotes: 0

Views: 4082

Answers (1)

djikay
djikay

Reputation: 10628

Have you tried replacing this:

"S:\WildlifeHealth\Idaho\Incoming\test.bat var1"

with this:

"S:\WildlifeHealth\Idaho\Incoming\test.bat " & var1

in the Shell function of your OpenWB sub?

To understand what's going on, your original code was constructing the command inside a string literal, i.e. a fixed string. So it looked that you really wanted to pass the argument called var1. In reality what you wanted to do is replace the value of var1 with the parameter to your sub called var1. This means that you wanted to construct your batch command at run-time, which means you needed to append the supplied value of var1 to the batch file. Therefore, your original code was hardcoding var1 as a parameter whereas the updated code is appending whatever the run-time value of var1 is to the batch file as a parameter.

Upvotes: 2

Related Questions