Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing string as macro parameter
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing parameter to a ms query | Excel Programming | |||
Passing chartobject as a parameter to sub | Excel Programming | |||
VB macro - Nested Calls ( Parameter Passing ) | Excel Programming | |||
Passing a Sub's name as parameter | Excel Programming | |||
Passing parameter to a query | Excel Programming |