user3781528 user3781528 - 26 days ago 10
R Question

issue with R script execution from Access 2016 command button

I’m trying to execute R script from Access 2016 command button. The script reads in a file and generates a plot from values inside the file.
It works well when I run it from Windows 10 command line as follows:

C:\Program Files\R\R-3.3.2\bin>Rscript --vanilla H:\R\analyze.R H:\R\LCH_22.vcf


I would like to execute the R script from a button in Access 2016.

Here is a vba script that I'm using:

Dim file_path As String

file_path = "H:\R\LCH_22.vcf"

Dim RetVal
RetVal = shell("""C:\Program Files\R\R-3.3.2\bin\Rscript.exe"" --vanilla ""H:\R\analyze.R"" ""H:\R\LCH_22.vcf""", vbHide)

MsgBox RetVal


The script executes but generates a blank plot. It has all axis and labels but all data is missing. I'm not sure why this is happening.

Follow up I've replaced
vbHide
with
vbNormalFocus
and was able to generate a plot with data... yeah! However, I don't understand why substituting a variable into Shell command doesn't work. Could someone please explain how to accomplish this correctly? I will read the file name from a form in Access and this part of the code has to be dynamic (See example of my attempt below).

Dim file_path
file_path = "C:\R\DNA.vcf"
Dim RetVal
RetVal = shell("""C:\Program Files\R\R-3.3.2\bin\Rscript.exe"" --vanilla ""C:\R\analyze.R"" "" & file_path & """, vbNormalFocus)


Thank you very much for your help in advance.

Answer

Simply add one more pair of quotes around file_path:

Dim file_path As String
Dim RetVal As Integer

file_path = "C:\R\DNA.vcf"

RetVal = shell("""C:\Program Files\R\R-3.3.2\bin\Rscript.exe"" --vanilla "_
               & """C:\R\analyze.R"" """ & file_path & """", vbNormalFocus)