Running R script from Access module

[rccline]

I would like to run an R script I have named "iterate.R" from an MS Access module via a command button on a form.

I found the following code on StackOverflow, but something is wrong when I substitute my path.

Link

Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""C:\Path\To\R\script.R"""    
   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub

Thank you.

 

[Andrzejek]

but something is wrong" - any errors?

If you want to put double quotes around "C:\Path\To\R\script.R", you may try:
[tt]path = "RScript "[blue] & Chr(34) & [/blue]"C:\Path\To\R\script.R" [blue]& Chr(34)[/blue]
Debug.Print path
[/tt]
You get:[tt]
RScript "C:\Path\To\R\script.R"[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

I think we need to see the code you are actually using, rather than the example you are trying to follow

 

[lameid]

CODE --> VBA
Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""C:\Path\To\R\script.R"""    
   [red]Debug.print DIR(path) 'This should put the file name of your R Script in the immediate window if it exists[/red]

   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub

 

[rccline]

I created a form with a command button to run on click:

I am getting an error:

Private Sub Command5_LostFocus()
' Sub RunRscript()
   Dim shell As Object
   Dim path As String
   Dim waitTillComplete As Boolean: waitTillComplete = True
   Dim style As Integer: style = 1
   Dim errorCode As Integer

 '  comment out:  DoCmd.TransferText acExportDelim, , "qryToExport", "C:\Path\To\CSV.csv"

   Set shell = VBA.CreateObject("WScript.Shell")

   path = "RScript ""F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"""
   Debug.Print Dir(path) 'This should put the file name of your R Script in the immediate window if it exists

   errorCode = shell.Run(path, style, waitTillComplete)

   Set shell = Nothing
End Sub

Private Sub Detail_Click()

End Sub

R script:

r_function <- function(a) {
  for(i in 1:a) {
    b <- i^2
    print(b)
  }
}

r_function(5)

 

[Andrzejek]

I am getting an error" on which line of code?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[rccline]

The error occurs on Debut.Print Dir(path)
Error 52: Bad file name or number

 

[strongm]

>Debug.Print Dir(path) 'This should put the file name of your R Script in the immediate window if it exists

No, it does not, since path doesn't actually cvontain a path at this point; it containes a command

>Error 52: Bad file name or number
See above

This can be fixed by replacing

path = "RScript ""F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"""
Debug.Print Dir(path) [COLOR=green]'This should put the file name of your R Script in the immediate window if it exists[/color]

with

path = "F:\D_Documents\GitHub-DasRotRad\access_run_script\r_function.R"
Debug.Print Dir(path) [COLOR=green]'This should put the file name of your R Script in the immediate window if it exists[/color]
Path = "RScript """ & path & """

 

[strongm]

>a command button to run on click:

The code you have showed us is not the Click event code

Indeed, your reported error message "[tt]You didn't specify Search criteria [][/tt]" suggests that you have an (incomplete) macro bound to the button in question, which takes precedence over the Click event