Instead of putting them in an environment variable, how about just creating a
text file and putting them there.


andrewsc wrote:

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.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson