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

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.

CodePudding user response：

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

CodePudding user response：

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