How to pass parameters to excel from wscript (Windows script)

  • Thread starter Thread starter Denis
  • Start date Start date
D

Denis

I have a windows script (wscript.exe) that I use to call excel from a batchscript. Now I have an excel application where I need to pass in an excel parameter using this wscript. I understand parameters can be passed in viathe /e tag (eg, /e/parm1/parm2/...) but I don't know how I can do this from the wscript. Unfortunately, I haven't been able to find anything by googling or searching this group. Anybody know how to do this?

Here's my wscript:

Dim xlApp

Set xlApp = CreateObject("Excel.application")

Set xlWb = xlApp.workbooks.Open("c:\somedir\...\some.xls")

xlApp.Quit

Set xlWb = Nothing
Set xlApp = Nothing

Denis
 
I have a windows script (wscript.exe) that I use to call excel from a batch script. Now I have an excel application where I need to pass in an excel parameter using this wscript. I understand parameters can be passed in via the /e tag (eg, /e/parm1/parm2/...) but I don't know how I can do this from the wscript. Unfortunately, I haven't been able to find anything by googling or searching this group. Anybody know how to do this?



Here's my wscript:



Dim xlApp



Set xlApp = CreateObject("Excel.application")



Set xlWb = xlApp.workbooks.Open("c:\somedir\...\some.xls")



xlApp.Quit



Set xlWb = Nothing

Set xlApp = Nothing



Denis
Click to expand...

I think I have found the solution to my problem. I create a helper macro in my .xls and then call this helper macro from my script with my parameter(s). This looks like this:

Set xlWb = xlApp.workbooks.Open("c:\somedir\...\some.xls")
xlApp.run "HelperMacro", "SomeParameterValue"

This HelperMacro does what needs to be done with whatever parameter(s) is passed to it.

This isn't an ideal solution for my application since this .xls (which I inherited) is set to automatically run when the above workbooks.Open above isexecuted. But I know a way to work around that.

If there is a way to pass in a parameter(s) directly when the workbooks.Open is executed I would still be interested in such a solution but I can livewith this Helper Macro approach.

Denis
 
Denis said:
If there is a way to pass in a parameter(s) directly when the workbooks.Open is executed I would still be interested in such a solution but I can live with this Helper Macro approach.
Click to expand...



check that

http://www.vbforums.com/showthread....rameter-DKenny-is-KING!&p=2234990#post2234990


The idea is to read command line parameters using that

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW"
() As Long

but read entire post to see how these parameters must be prepared as
command line arguments.
 
check that



http://www.vbforums.com/showthread....rameter-DKenny-is-KING!&p=2234990#post2234990





The idea is to read command line parameters using that



Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW"

() As Long



but read entire post to see how these parameters must be prepared as

command line arguments.
Click to expand...

Yes, I saw how you can use this GetCommandLine to retrieve command line parameters and you can set command line parameters using the /e tag. However,I haven't found anything that shows whether you can set these command lineparameters when a .xls is invoked from a visual basic script (eg, workbooks.Open(...xls). Probably that's because there really isn't a "command line" in this case. If there were a way to do this, then this command line approach would be preferable to a helper macro.

Denis
 
I'm inclined to go with something like this in that workbook' _Open
event...

Application.Run "HelperMacro", "SomeParameterValue"

...so your process runs when the workbook opens.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
OR more simply...

Call HelperMacro(SomeParameterValue)

...where both my suggestione assume the procedure is stored in the xls.
Otherwise, you need to ref the external source workbook!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top