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)) |
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)) |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com