Run R script from VBA

Question

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?

AHeyne · Accepted Answer · 2020-04-10 08:38:56Z

1

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

edited Apr 10, 2020 at 8:38

answered Apr 10, 2020 at 8:32

AHeyne

3,5052 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anais Over a year ago

Thank you! Does that mean I should necessarily use a batch file and go through Notepad and Cmd? I'm not sure I understand how to apply this explanation to my VBA script (I'm very new at VBA)

AHeyne Over a year ago

As I don't use R at all, I cant state that explicitly. I would go and test if providing the absolute path already fulfills your needs. If not it seems that you will have investigate in that batch idea.

ASH · Accepted Answer · 2020-04-10 14:28:21Z

1

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

answered Apr 10, 2020 at 14:28

ASH

20.5k28 gold badges117 silver badges247 bronze badges

1 Comment

Anais Over a year ago

Almost working with your second path! (the one without the CMD BATCH part) It opens a window with R but says "ARGUMENT '[path to the script]' ignored

Anais · Accepted Answer · 2020-04-16 07:29:59Z

0

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

answered Apr 16, 2020 at 7:29

Anais

891 silver badge10 bronze badges

2 Comments

Doug Kimzey Over a year ago

I could not get this to work at all. It returns Method 'Run' of 'IWshShell3' failed.

Doug Kimzey Over a year ago

Change sPath = """" & sRApplicationPath & """" TO chr(34) & sRApplicationPath & chr(34) and this works.

Collectives™ on Stack Overflow

Run R script from VBA

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related