Excel Parameters???

  • Thread starter Thread starter Telly Massaras
  • Start date Start date
T

Telly Massaras

How can I pass values to an Excel Workbook when invoking
Excel from from the command line?
 
Telly

Are the values specific or is it the time/date or something? If it is that
you could set a workbook_open event as this is triggered on opening.

Apart from that AFAIK there is no way 'naturally' to pass values to sheets
from the command line

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick,

Thanks for your reply. The values that I want to pass in will be
different every time. One value will be a string and the other is an
number. Once the data is read-in to Excell, it will passed into a macro
which will use the values to form a file name from which to read data.
How would the workbook_open event work? It's triggered upon start-up
but how do the values get into Excell?

Thanks,

Telly...
 
Can you not use the /cmd on the command line and then parse whatever
follows /cmd with the COMMAND function in the open_event?

martin
 
Martin,

I'm not familiar with the /cmd command line switch in Excel (if that is
what you are refering to) or the COMMAND option in the open event. Can
you offer more information of how this might work?

Telly...
 
Telly

Here's an example of a Workbook_Open event that prompts the user for data
whenever the workbook is opened

Private Sub Workbook_Open()

Dim x As Variant

x = Application.InputBox("Enter information")

MsgBox x

End Sub

From where is the data that you want to include coming? Will a user be
typing it in the command line, or is it coming from another program?
 
Telly,

I was about to ask the same question, because the solution I have is
not perfect. You could use it, neverhteless.

I am starting XLS files from BAT scripts. The Auto_Open macro needs to
open a CVS file, formats it, saves it as XLS, then exist. Just like
yourself, I need to pass some parameters to the Auto_Open macro (in my
case, two strings).

My solution is to use environment variables. The BAT script sets the
values, for example
SET MYVAR=c:\alpha\beta\gamma.txt
Then, in VBA code, I have

Dim mystr as String
mystr = Environ("MYVAR")

This works quite nicely, except ....
If excel is already running when I invoke the XLS file from BAT, then
no new process is started. Instead, Windows is using DDE to talk to the
pre-existing Excel process, and make it open my XLS file wuith the
macro.

If the Exel process was started up beforehand, then it does not inherit
the environment variables from my BAT file.

In a nutshell, this solution only works if no other instance of Excel
is running.
 
Instead of putting them in an environment variable, how about just creating a
text file and putting them there.
 
Back
Top