Passing string as macro parameter

[kmbarz]

I'm working with some test macros to try and get something real working.

Within VBA, I can call the test2 macro with a string parameter and
everything works:

Sub test2(LOSName)
MsgBox LOSName
End Sub

Sub test3()
test2 ("Hi")
End Sub

However, if I try the same thing from a command line going through my VBS
program, my double quotes are getting stripped away somewhere and I can't
pass in my parameter.

c:\> cscript.exe c:\CallExcelMacro.vbs c:\test.xls test2("Hi")

results in: CallExcelMacro.vbs(14,3) Microsoft Office Excel: The macro
'test2(Hi)' cannot be found.

Any ideas as to what the issue here is? Thanks. The vbs follows:

Option Explicit
Dim objArgs
Set objArgs = WScript.Arguments


Sub CallExcelMacro(strData, strMacro)

Dim objXL
'Dim strData

Set objXL = WScript.CreateObject("Excel.Application")
objXL.WorkBooks.Open strData
objXL.Visible = TRUE
objXL.Run(strMacro)
objXL.Quit
Set objXL = Nothing
End Sub

Call CallExcelMacro(objArgs(0), objArgs(1))

 

[John Coleman]

In your VBS program you need to pass the macro name and the macro
argument(s) as separate paramters to the run method.

Something like

Sub CallExcelMacro(strData, strMacro, strArg)
Dim objXL
Set objXL = WScript.CreateObject("Excel.Application")
objXL.WorkBooks.Open strData
objXL.Visible = TRUE
objXL.Run(strMacro, strArg)
objXL.Quit
Set objXL = Nothing
End Sub

Maybe you should close the workbook before the Quit? I usually do, but
it may not be strictly required.
In any event,t hen replace your call by

Call CallExcelMacro(objArgs(0), objArgs(1), objArgs(2))

and your command line by

c:\> cscript.exe c:\CallExcelMacro.vbs c:\test.xls test2 Hi

or something along those lines. The main point is you need to pass the
macro name and the arguments separately

HTH

-John Coleman