Shell hitting Run-Time error '5' trying to call R script, tried everything in Access VBA

Michael Bates 0
Viewed 58

So I have made a simplified version of my code that directly highlights the issue. I have searched and read dozens of similar issues/solutions on SO today and none have worked so I'm making my own question as a last resort, hopefully its a simple one.

Essentially part of my workflow in VBA in Msoft Access involves calling an R script that does some logic and returns information into a table in the same database, simple stuff. It was working fine up until yesterday when we moved the location of the R installation to a new drive. Changing the path to this new install location just flat does not work, no other code is changed.

 cmd = "C:\R\bin\i386\Rscript.exe C:\R\test.R"
 Debug.Print cmd
 Shell cmd

I get runtime error '5'. I am using the immediate window to check the paths are correct and copying them into RUN to verify that they do indeed work.

The above outputs:

C:\R\bin\i386\Rscript.exe C:\R\test.R

And works fine in RUN.

The first thing I found when searching online is to add more (") as shell can handle them weird:

cmd = """C:\R\bin\i386\Rscript.exe""" & " " & """C:\R\test.R"""

Or any iterations of using "s in different places, output:

"C:\R\bin\i386\Rscript.exe" "C:\R\test.R"

Same error but works fine in RUN. I have also tried them all successfully in CMD too. It just seems that shell refuses to launch R from that path. I have moved it elsewhere on my C drive with same effect.

I am really at a loss, its a very simple piece of my overall pipeline but shell just can't seem to get there, ideas? I cannot recreate the original R installation path as that shared drive is now completely dead.

EDIT:

After trying many more things I changed to using Shell execute simply to try and make Notepad ++ open, again works in cmd.

 Set objShell = CreateObject("Shell.Application")
 objShell.ShellExecute "C:\N\notepad++.exe", "C:\R\test_in.csv", "", "open", 1

This time I hit a "suspicious macro error" that leads me to believe that it may be an antivirus setting (macros are enabled in access) blocking shell from calling anything.

ms-access shell vba
2 Answers

After days of testing I have found the solution, hopefully this can help anyone else in a similar situation. Windows Defender only blocks shell calls to non-Microsoft products, so I nested a call to PowerShell within the call to Shell:

Shell ("powershell.exe C:\R\bin\i386\Rscript.exe C:\R\test.R")

Take note you need to play around with the "s a lot ot get it working, my actual pipeline has more arguments and I had to enclose them in 5 sets of "s for it to pass through to powershell properly. IE:

 Dim codePath As String: codePath = """""\\example\example"""""
Michael Bates0

Try these variations using Start or a second Command:

cmd = "Start C:\R\bin\i386\Rscript.exe C:\R\test.R"

or:

cmd = "cmd /c ""C:\R\bin\i386\Rscript.exe C:\R\test.R"""
uservDNe0
Related