CODEWITHSUNDEEP
rvba
Anais
Anais

Reputation: 89

Run R script from VBA

I have tried several examples found on forums to run my R script from VBA, but it doesn't work. The R script works well alone. Here is the code I ran:

Sub RunRscript()

Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Integer: style = 1
Dim errorCode As Integer
Dim path As String
path = "RScript C:\Users\Documents\Code.R"
errorCode = shell.Run(path, style, waitTillComplete)

End Sub

I'm getting an error message saying that running object "IWshShell3" failed. Is there anything special to write on the R code prior to running this macro? Shall I load a package, or load the files in a specific folder?

Upvotes: 1

Views: 1522

Answers (3)

Anais
Anais

Reputation: 89

Finally, here is a solution working well:

Function Run_R_Script(sRApplicationPath As String, _
                    sRFilePath As String, _
                    Optional iStyle As Integer = 1, _
                    Optional bWaitTillComplete As Boolean = True) As Integer

Dim sPath As String
Dim shell As Object

'Define shell object
Set shell = VBA.CreateObject("WScript.Shell")

'Wrap the R path with double quotations
sPath = """" & sRApplicationPath & """"
sPath = sPath & " "
sPath = sPath & sRFilePath

Run_R_Script = shell.Run(sPath, iStyle, bWaitTillComplete)
End Function

Sub Demo()
Dim iEerrorCode As Integer
iEerrorCode = Run_R_Script("C:\Program Files\R\R-3.6.1\bin\x64\Rscript", 
"C:\Users\myname\Desktop\Code.R")
End Sub

Upvotes: 0

ASH
ASH

Reputation: 20362

This is how I would do it.

Sub RunRscript1()
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorCode As Integer
    Dim path As String

    ' path to R executable:  C:\your_path\Documents\R\R-3.2.5\bin\x64\R.exe
    ' path to R script:  C:\your_path\Documents\R\Download.r
    ' see more setup details here
    ' http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html
    path = "C:\your_path\Documents\R\R-3.2.5\bin\x64\R.exe CMD BATCH --vanilla --slave C:\your_path\Documents\R\Download.r"
    'path = """C:\your_path\Documents\R\R-3.2.5\bin\i386"" C:\Users\rshuell001\Documents\R\Download.R"
    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

Upvotes: 1

AHeyne
AHeyne

Reputation: 3475

Your script is not able to find the executable (RScript). Provide the absolute path, then it should work well.

See here on where to find it: http://datacornering.com/how-to-run-r-scripts-from-the-windows-command-line-cmd/

Edit:
I saw right now, that you could stumble into further problems regarding missing environments.
See here: Setting .libPaths() For Running R Scripts From Command Line Using Rscript.exe

Upvotes: 1

Related Questions