Environment Variables not recognized when calling Run in WScript.Shell object

Question

If I run the command Rscript "C:/TEMP/test.R" in the command line it works and my script runs as expected. Once I try to run it in my VBA code it does not recognize the Rscript as a valid command.

Dim shell_obj As Object
Set shell_obj = VBA.CreateObject("WScript.Shell")
Dim errorCode As Integer
errorCode = shell_obj.Run("Rscript ""C:/TEMP/test.R""", 1, True)

When I looked into the PATH variable being used by the WScript.Shell I saw that it does not include the System Variables with the Rscript path inside of it.

Dim shell_obj As Object
Dim wshSystemEnv As Object
Set shell_obj = VBA.CreateObject("WScript.Shell")

' This one does not include the path to the Rscript'
Debug.Print shell_obj.ExpandEnvironmentStrings("%PATH%")
Set wshSystemEnv = shell_obj.Environment("SYSTEM")
' This one includes the path to the Rscript'
Debug.Print wshSystemEnv("PATH")

Can I force the the WScript.Shell object to use the System environment? Or at least use its variables?

Cmd:

VBA (version 1):

VBA (version 2):

In VBA, just use Environ("path"). No WScript or RScript necessary. — ashleedawg
So your problem is that the system isn't automatically checking the path where rscript.exe is located, correct? Do you know what the correct location of that fie is? (One you determine that, we can add it to the existing path.) — ashleedawg
I know it in my computer. But the code may be used in someone else's machine with a different version of R (which changes the path). So putting a fix path would not solve the problem. — Daniel
You had not mentioned that there are other computers involved. I'm now confused: your question says you can run it from the command prompt but not from VBA. The first step is to get it running on yours, even if the path is hard-coded for now. Where is rscript.exe located on your computer? Once you get this working on your machine then we can worry about others... — ashleedawg
Based on the research I've done, this file has greatly varying paths as there isn't a "standard" install location for the R package. I can help with code to programmatically locate the file, but I'm not going to bother until the script runs properly on your machine with a known path. — ashleedawg

ashleedawg ashleedawg · Accepted Answer · 2018-07-25T00:41:46

EDIT: See bottom of post.

Hopefully you'll find some use in my (lengthy) take on this... :-)

Testing for command-line readiness

Any command (including an RScript) that can be run as-is from the Windows command-line can also be run with either the VBA Shell function or the Windows WScript.Shell method.

The issue is, your cmd string is not command-line ready. This can be confirmed by hitting +R and pasting the contents of your cmd string variable:

Rscript "**path**/test.R"

I don't currently have rscript.exe installed but I suspect you will get an error if you try running your command manually in either the Run window or on the command line. If it doesn't run there it obviously won't run with in a VBA Shell.

As I understand it, the double asterisk is a Java notation the way you are using it, and in R is the same as a ^ caret character, which is for calculating exponents.

Referencing an environment variable

To return the Windows PATH environment variable in VBA, you would use VBA's Environ function.

To insert the value environment variable inline at the command line, you would surround it with %percent% symbols, like %path%

Windows' `PATH` environment variable

PATH does not return a single folder. It's a list of folders that Windows should check to find an executable file that one attempts to run.

^{When a command is entered in a command shell or a system call is made by a program to execute a program, the system first searches the current working directory and then searches the path, examining each directory from left to right, looking for an executable filename that matches the command name given.}

^{The Windows system directory (typically C:\WINDOWS\system32) is typically the first directory in the path, followed by many (but not all) of the directories for installed software packages.}

An example a default value of PATH (from a fresh install of Windows 7) is:

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

As with %path%, this includes%SystemRoot%` which, by default on Windows 7 is the string:

C:\Windows

Checking environment variables

You can verify the value of your PATH environment label:

Hit +R.
Type cmd and hit Enter. (A command line window should open.)
Type or paste echo %path% and hit Enter.

The contents of the Window PATH environment variable will be displayed.

You can also check environment variables from within Windows:

and type env (to search)
Click Edit the system environment variables. (There is a similar option "...for your account" which is not quite the same.)
Click

^{Note: Although you technically can change the PATH in this window, I would not recommend doing so, especially with PATH since it is split up into System and User folders, and Windows likes certain folders in certain areas, and some changes don't take effect until reboot but others do, and blah blah blah, trust me: it's easier to do from the command line.}

What's wrong with your code?

Therefore it based on all of this, it appears that the command you're trying to run is:

Rscript "C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem/test.R"

...which obviously will not work.

Get or set the current working folder/directory

I can only speculate as to what you're trying to accomplish.

I suspect you didn't intend to return the entire PATH variable, but are only interested in the current working folder.

If so, you don't need to specify a folder at all. Shell already commands execute in the "current" folder.

One way you can check which directory or folder is current, is with the VBA CurDir() function, like:

Debug.Print CurDir()

The value of CurDir can be changed with the ChDir statement.

Similar functions

Note that the CurDur() command is often confused with similar functions like:

Application.Path which returns the path to the Excel application, or,
ActiveWorkbook.Path which returns the location that the active workbook is saved (or an empty string if it's unsaved).

Possible Solution: _{^{(How to run an rScript in the current path in VBA)}}

If your R script and the rscript.exe are both in the current working folder, run it with just one line of VBA:
```
Shell "rscript.exe test.R", vbNormalFocus
```
If you require VBA to wait for execution of the Shell command to complete before resuming VBA, then you can you just this one line:
```
CreateObject("WScript.Shell").Run "rscript.exe test.R", vbNormalFocus, True
```

More Information:

^{I generally make a point of including links to any sites I used to verify my answers, so this must be my most-researched answer yet because I've never had a list this long... and I left some off out this time!}

Stack Overflow : Running R scripts from VBA
MSDN : Shell Function (VBA)
MSDN : Environ Function (VBA)
R-Bloggers : Passing arguments to an R script from command lines
Stack Overflow : R.exe, Rcmd.exe, Rscript.exe and Rterm.exe: what's the difference?
Stack Exchange: Statistics : Double star ** in R?
Wikipedia : PATH (variable)
Stack Overflow : Default values of PATH variable in Windows 10
SuperUser : Default PATH for Windows 7
MSDN : CurDir() Function (VBA)
MSDN : ChDir() Statement (VBA)
MSDN : Application.Path (Excel/VBA)
MSDN : ActiveWorkbook.Path (Excel/VBA)
Stack Overflow : Wait for Shell command to complete
MSDN Forums : Difference between wscript.shell and shell.application
Stack Overflow : Steps to run R script through Windows command prompt

One More Demo of What's Wrong With Your Code:

I have a batch file named test.bat located in C:\WINDOWS. My PATH environment variable contains C:\WINDOWS (among other things).

If I go to the command prompt in root folder C:\ and type test.bat:

...it runs properly (even though my file is not in that folder... since the c:\windows folder is within the PATH variable.)

However, if I go to the command prompt and type C:\test.bat:

...it does not work. It cannot find the file because I specified a folder where the file is not located.

--- In VBA, if I run the command Shell "test.bat",1:

...it runs properly (even though my file is not in that folder... since the c:\windows folder is within the PATH variable.)

However, if in VBA I run the command Shell "c:\test.bat",1:

...it does not work. It cannot find the file because Ispecified* a folder where the file is not located**.

Both VBA and the Shell command are behaving the same way, when given the same information.

Environment Variables not recognized when calling Run in WScript.Shell object

2 Answers

EDIT: See bottom of post.

Testing for command-line readiness

Referencing an environment variable

Windows' `PATH` environment variable

Checking environment variables

What's wrong with your code?

Get or set the current working folder/directory

Similar functions

Possible Solution: _{^{(How to run an rScript in the current path in VBA)}}

More Information:

One More Demo of What's Wrong With Your Code:

Environment Variables not recognized when calling Run in WScript.Shell object

2 Answers

EDIT: See bottom of post.

Testing for command-line readiness

Referencing an environment variable

Windows' PATH environment variable

Checking environment variables

What's wrong with your code?

Get or set the current working folder/directory

Similar functions

Possible Solution: (How to run an rScript in the current path in VBA)

More Information:

One More Demo of What's Wrong With Your Code:

Windows' `PATH` environment variable

Possible Solution: _{^{(How to run an rScript in the current path in VBA)}}