CODEWITHSUNDEEP
vbaexcelbatch-file
skatun
skatun

Reputation: 877

using environment variables in excel

So I am using this code in excel to read environment parameters on startup:

Dim ExcelArgs As String
Dim arg As String
ExcelArgs = Environ("ExcelArgs")

MsgBox ExcelArgs
If InStr(UCase(ExcelArgs), "CREO") >= 0 Then
    Application.DisplayAlerts = False
    If Len(ExcelArgs) > Len("CREO") Then
        arg = Split(ExcelArgs, ",")(1)
        Call Creo.addNewPartToPartslist(arg)
    End If
    Application.DisplayAlerts = True
End If

and this line in my batch script:

echo "Launch excel"

Set "ExcelArgs=CREO,DXFWITHOUTDRW

"C:\Program Files (x86)\Microsoft Office\OFFICE16\Excel.exe" /r "%APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB" 

exit 0

The problem is that if i run the batch file once, keep excel open change the excelargs to CREO,wqhatever in batch file and rerun batch file the excelargs, dos not get updated!!!

So my theory is that excel either caches out the environment variable or that if it is being used by one instance the batch script can not set it

link with some info about passing arguments to excel: https://superuser.com/questions/640359/bat-file-to-open-excel-with-parameters-spaces

Upvotes: 3

Views: 2830

Answers (2)

skatun
skatun

Reputation: 877

Excel is open Excel is open

Then i start the batch script:

batch start

The it does not open it as read only by default, but prompt me instead, not a big issue but a bit annoying, and it also make it impossible to loop through to run the batch several times for different input parameters.

enter image description here

A bit unsure how I should post this, couldnt paste images in comments, and to edit the the original question, which was how to start excel with enviroment variable in new instance (/x did the trick), but now /r does not work, Should I post as new question and refer to this one or can I leave it as an answer?

Upvotes: 0

MC ND
MC ND

Reputation: 70933

Usually excel sees if there is a previous instance running and let this instance handle the file opening.

Is this important? Yes, in your case both requests to open the file are handled by the same excel process.

How does it make a difference? Environment variables are not shared. Each process has it own environment block that is initialized when the process is created (can be a customized block or a copy of the environment of the parent process) and once the environment is created for a process, only this process can change its environment.

In your case, when you start excel the new process gets a copy of the environment block of the cmd process. Later, when you change the cmd environment, the already running excel instance sees no changes in environment and, as the new request to open excel is converted to a request to the previous process, there is not a new process with a new copy of the cmd environment with the changes.

The only way I see to make it work is to force excel to start a new process (that will inherit the changes in the cmd instance) instead of reusing the previous one.

But this depends on the excel version. As far as I know, the 2013 version includes an /x switch to force separate process usage. For previous versions, maybe this question, or this one could help you.

Upvotes: 3

Related Questions