Passing string as macro parameter
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
kmbarz wrote:
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))
|