1

I'm trying to run an R Script from my access DB so it will process some data before it is imported, however i'm having trouble as it fails at the errrorCode on the last line.The scrip is: 

 Private Sub cmdRcode_Click()
 Dim shell As Object
 Set shell = VBA.CreateObject("WScript.Shell")
 Dim waitTillComplete As Boolean: waitTillComplete = True
 Dim style As Integer: style = 2
 Dim errorCode As Integer
 Dim path As String
 path = "C:\Users\Liam\Dropbox (BGD_058)\BGD_058\19_BFI_data\Analysis\r_scripts\BFI_DAT_1.R"
 errorCode = shell.Run(path, style, waitTillComplete)
 End Sub

EDIT:

Upon advice from the comments i've changed my file path, avoid spaces and substituted the errorCode as follows:

path = "Rscript C:\Users\Documents\BFI_Field_Forms\R\BFI_DAT_1.R"
errorCode = shell.Run("C:\Users\R\BFI_DAT‌​_1.R", 1, True)

I then updated the environment path to Advanced System Settings/Environment Variable and created a new variable called "PATH" with the line: C:\Program Files\R\R-3.2.2\bin\x64;

The result is that it opens my R file, however it does not run the R script. The priority is for it to run the script (so the data is processed), and if possible, the R file would not be visible to the user.

Any advice appreciated.

cosi
  • 21
  • 1
  • 5
  • To execute your `R` file the shell has to know with what program; simply put `Rscript ` at the beginning of `path` (`path= "Rscript C:\.."`) – wici Nov 25 '16 at 10:54
  • Plus be careful about the space in your folder name. WHen there are spaces, full path has to be quoted, so you can double quotes or use CHR(34) as in: http://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba – Eric Lecoutre Nov 25 '16 at 12:20
  • It's still failing to execute. I've tried different paths with no spaces but it hasn't worked. Any other diagnostic suggestions? – cosi Nov 26 '16 at 07:55
  • 1
    Do you have *Rscript* as an environment path variable? What happens when you run path with Rscript as @wici suggests in PowerShell or DOS cmd prompt? – Parfait Nov 26 '16 at 15:18
  • Yes, I just updated the environment path and can execute via cmd. The code now works however it only opens the R file, it does not run the script which is what i need it to do... – cosi Nov 26 '16 at 15:49
  • So it seems it's very close! I've updated the User Variables to `C:\Program Files\R\R-3.2.2\bin\x64;` but it still only opens the Rstudio file both via my cmd prompt and access combo button. @Parfait – cosi Nov 29 '16 at 18:27
  • What is an R file? Or RStudio file? There are only .R scripts which opens in R terminal, R shell, or Rstudio programs. – Parfait Nov 29 '16 at 19:15
  • I refer to my .R script which opens in Rstudio when i run my code. However the script does not run, it only opens the console meaning none of the script is processed. – cosi Nov 30 '16 at 01:27

0 Answers0