use variables in excel 2010 vba with batch file

[cmccabe1]

I am trying to update variable in an excel VBA that are used with a batch file. The below doesen't look right but hopefully it is a good start. I set the batch file to just print the variables to ensure that they are being passed. Thank you :).

VB


' CALL AND UPDATE PERL '
Dim var As String, var2 As String, var3 As String, var4 As String
var = "MyDirectory"
var1 = MyDirectory & "sample_descriptor.txt"
var2 = "C:\cygwin\home\cmccabe\test_probes8.txt"
var3 = MyDirectory & "output.txt" (file that will result)

Set wshell = CreateObject("wscript.shell")
wshell.Run Chr(34) & "C:\Users\cmccabe\Desktop\EmArray\Design\perl.bat, var"

Batch File (perl.bat)


set "a=%1"
set "b=%2"
set "c=%3"
set "d=%4"
echo The directory is %a%
echo The samples are %b%
echo The probes are %c%
echo The output is %d%
pause

[SamT]

Set your Vars to be uses By Perl.

Note Comma+Space in all
var = "MyDirectory, "
var1 = MyDirectory & "sample_descriptor.txt" & ", "
var2 = "C:\cygwin\home\cmccabe\test_probes.txt, "
var3 = MyDirectory & "output.txt"
PerlCommand = "C:\Users\cmccabe\Desktop\EmArray\Design\perl.bat"

wshell.Run Chr(34) & PerlCommand & ", " & Var & Var2 & Var3

Or use ... & Var & ", " & var2 & ", " & ...

[Paul_Hossler]

I'd suggest bracketing the parameters with a quote in case there are spaces in a file name



Option Explicit
Sub PHH()
Dim PerlCommand As String, PerlParameters As String, MyDirectory As String
Dim var As String, var1 As String, var2 As String, var3 As String

MyDirectory = "C:\Users\Daddy\Scripts\"
var = MyDirectory
var1 = var & "sample_descriptor.txt"
var2 = "C:\cygwin\home\cmccabe\test_probes.txt"
var3 = var & "output.txt"

' PerlCommand = "C:\Users\cmccabe\Desktop\EmArray\Design\perl.bat"
PerlCommand = """C:\Users\Daddy\Scripts\perl.bat"""
PerlParameters = """" & var & """" & " " & _
"""" & var1 & """" & " " & _
"""" & var2 & """" & " " & _
"""" & var3 & """"

MsgBox PerlCommand
MsgBox PerlParameters


CreateObject("wscript.shell").Run PerlCommand & " " & PerlParameters
End Sub




Also I think the bat file needed a little work, since the set= lines were in quotes




Set a=%1
Set b=%2
Set c=%3
Set d=%4
echo The directory Is %a%
echo The samples are %b%
echo The probes are %c%
echo The Output Is %d%
pause

[cmccabe1]

Thank you :)