CODEWITHSUNDEEP
vbashellms-access
Michael Bates
Michael Bates

Reputation: 95

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

I made a simplified version of my code that directly highlights the issue.
I have read dozens of similar issues/solution.

Part of my workflow in VBA in Microsoft Access involves calling an R script that does some logic and returns information to a table in the same database.
It was working until we moved the location of the R installation to a new drive. Changing the path to this new install location 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 work.

The above outputs:

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

It works in RUN.

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

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 in RUN. I also tried them all successfully in CMD.

It seems just Shell refuses to launch R from that path. I have moved it elsewhere on my C drive with same effect.

I cannot recreate the original R installation path as that shared drive is now completely dead.

EDIT:

I changed to using ShellExecute 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.

Upvotes: 1

Views: 526

Answers (2)

Michael Bates
Michael Bates

Reputation: 95

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"""""

Upvotes: 1

Gustav
Gustav

Reputation: 55906

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"""

Upvotes: 0

Related Questions