Possible to pass a variable to Excel from command line/short cut?

[Jon]

We've got an application front we've built which uses an Oracle back
end. It has poor graphics/analytics, and we'd like to use Excel for
that portion.

The only problem is that when we open Excel and connect to Oracle, we
still have to enter the ID# in Excel by hand.

We'd love be able to do something like run "analysis.xls /set/
ID=12345", and have it pass in ID=12345 as a variable to be used.
We're pretty good at developing Excel (use ADO pulls of Oracle data,
heavy into pivot tables and use buttons and forms coded through
VBA.)

But, we've never tried this.

I read the MS files about passing command line launch parameters via
"excel.exe ....", but didn't see anything about dumping named
variables in. (I might use them to force a "read only" open.)

We've thought about saving the ID to an Oracle database table, and
then having the Excel "on open" code check to see if there's an ID in
the last 30 seconds. However, this will be a multi-location, multi-
user application, and we'd rather not have to deal with figuring out
which row to open. (i.e. 2 people opening the Excel file at once.)

So, to restate the question:
Is there any way to pass a value to Excel (to VBA is okay) when
opening the file?

Thanks,

Jon

 

[JP]

As far as I can tell, only another program using VBA to automate Excel
can pass a variable to a new instance of it.


HTH,
JP

 

[Steve Yandl]

Jon,

You can use a VBScript file as a helper file. The WScript object has an
'arguments' property that you can use to retrieve the arguments when you
launch wscript.exe or cscript.exe with the vbs file as the first argument
and arguments to the vbs file following. The vbs file would contain the
code to create an instance of the Excel application, to open your designated
workbook, and if appropriate, run a specific subroutine, handing the sub
your argument.

Steve Yandl

 

[Jon]

We've got an application front we've built which uses an Oracle back
end.  It has poor graphics/analytics, and we'd like to use Excel for
that portion.

The only problem is that when we open Excel and connect to Oracle, we
still have to enter the ID# in Excel by hand.

We'd love be able to do something like run "analysis.xls /set/
ID=12345", and have it pass in ID=12345 as a variable to be used.
We're pretty good at developing Excel (use ADO pulls of Oracle data,
heavy into pivot tables and use buttons and forms coded through
VBA.)

But, we've never tried this.

I read the MS files about passing command line launch parameters via
"excel.exe ....", but didn't see anything about dumping named
variables in.  (I might use them to force a "read only" open.)

We've thought about saving the ID to an Oracle database table, and
then having the Excel "on open" code check to see if there's an ID in
the last 30 seconds.  However, this will be a multi-location, multi-
user application, and we'd rather not have to deal with figuring out
which row to open.  (i.e. 2 people opening the Excel file at once.)

So, to restate the question:
Is there any way to pass a value to Excel (to VBA is okay) when
opening the file?

Thanks,

Jon

I finally found a few links just recently that talk about how to deal
with this.

You can read the command line argument in VBA, and if you pass it
properly, you can parse out your info from the command line.

http://www.mrexcel.com/archive/VBA/15657.html is a 2001 write-up of a
"tip" letter from 1999.
http://www.dailydoseofexcel.com/archives/2006/01/25/command-line-arguments-in-vba/
is a 2006 article describing the same technique with a few comments.

In a nutshell, the format they use is

excel c:\temp\test /e/param1/param2/.../paramN

Then in VBA, use API function GetCommandLine (alias GetCommandLineA in
Win95), to retrieve in the command line string.

Once you have the string, it's up to your imagination how you'd like
to parse it out. (The sample code searches for the '/e', then grabs
the string until the next '/'.)

Jon