Please help - VBA isn't reading my Environment variable

Hi, sorry if this is a simple fix. I have created a user environment variable and named it OLIMARO.
This environment variable is supposed to point to the following folder: %userprofile%\documents\olimaro-2.3\
in the file path referenced above, I want to run a batch file named "run_olimaro.bat" via vba.

I have created this macro but it doesn't seem to be working
Sub RunOlimaro()
Dim folderPath As String
folderPath = Environ("OLIMARO")
Shell folderPath & "\run_olimaro"
End Sub


However placing the full path without specifying the environment variable seems to run.

Shell c:\Users\Chinoxl\documents\olimaro-2.3\

but I dont want to run the full path as I need it to run on a different computer where the folder path might be different.
What am I doing wrong? Please help.


EDIT: The solution was: Excel needs to be restarted each time the path in an existing environment variable is changed before the new path can be considered in the code. Using MsgBox in reading the value from the Environment variable path was helpful in recognizing what path was referenced in VBA.

If you add a MsgBox FolderPath statement, does it show the correct value? Also, you didn't actually say what happens when you run your macro? I'm guessing an error message, but if so, what?

Originally Posted by Chinoxl

I have tried to put the entire file path in a new environment variable and this works for some awkward reason, but when I break them using the above, it doesn't seem to work.

I'm not sure you followed everything I said. but...you know what the SHELL command is used for, right? it is meant to run actual programs:

https://docs.microsoft.com/en-us/off...shell-function

so, this:

Please Login or Register  to view this content.

does not point to a program. obviously that would not work. I hope we're on the same page here in terms of what I understand you're doing and what you *want* to do? also, did you look at Rorya's post?

Hi rorya,

Thanks for your suggestion, while battling with this yesterday, I tried to use Run instead of Shell (in the vba code I entered in my previous reply to @vba_php). I did this so the error message which shows "cant recognise macro" and then lists the path in the variable in the error message (my newbie attempt to replicate MsgBox ).
As you would expect, I discovered my vba was reading the wrong file path for the OLIMARO environment variable (I still dont know why). So I created a new environment variable with a different name, but pointing to the path I wanted and tried to check it with the run error message and it looked fine then I changed it to Shell. It ran fine, but then the problem is, It would'nt work unless I put the file path in the environment variable. If I split them into two so that I get the folder path and add the file to it in vba like this: Shell folderpath & "\run_olimaro" it doesn't run.

So why don't I want to use the full file path? The reason is because, I need to provide a form for the other users to specify the folder path on their computers, there are several files in the folder, if they have to specify the actual file to run (and there are several files at different path locations I have to create this for), it could work but then it would be a hassle for them to begin selecting each file and filling the file paths in the form. In essence, what I need is a way to launch the run_olimaro.bat file from the click of a button in vba (and several others like it).

The files were not installed, they were extracted from a zip folder which they have received via email. Therefore, getting the file path where they have chosen to extract the files to on their own computers seems impossible to me without some way for them to tell me the path (so I can write the vba to launch the file from each path separately). That's why I am using environment variables.

If there is some other way to do it without environment variables, that would make my day. Perhaps if there was a code for them to select the file in a dialog box and then this appears as a full file path somewhere, I can use this to create the environment variable for them.


EDIT: To answer your question the error code when I use shell in the vba is:

Run-time error '5'
invalid procedure call or argument