Error running RScript in Excel VBA

Question

I've searched through several solutions to this same problem, but my code still doesn't work. Made sure to not have spaces in my file path, and still have it triple quoted to be sure. I get the "Method 'Run' of object 'IWshShell3' failed" error from running the macro. What could I be missing here?

Code:

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 ""G:\structureshouston\Kocian\hello.R"""
    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

Can you actually run Rscript from your command line? Is it accessible via your Path environment variable? — MrFlick, Apr 14 '16 at 21:52
Is the `.r` file extension associated with `rscript.exe` ? Can you omit the program and run it by association? — , Apr 14 '16 at 22:01
I am able to run the Rscript from cmd, yes. It works perfectly when done that way. I actually just tried it out now and get no error messages. Made no changes over the weekend or anything else. So bizzare. — Matt K., Apr 19 '16 at 14:38

score 4 · Accepted Answer · answered Apr 15 '16 at 14:24

4

I believe your level of quotes is incorrect. The RScript should be in quotes while the filename for the script shouldn't be. Also, I like to make sure I include the full path names in the call. Try:

path = """C:\Program Files\R\R-3.2.4revised\bin\RScript"" G:\structureshouston\Kocian\hello.R"

You may need to update the path to RScript depending on the version you have installed.

answered Apr 15 '16 at 14:24

Dave2e

22,192
18
42
50

it helped me too. Can you please tell how to send variable to R with the path too – aman Aug 09 '17 at 04:18
1

@aman https://stackoverflow.com/questions/30698929/passing-variables-from-vba-to-r-script or https://stackoverflow.com/questions/51139735/how-to-pass-an-argument-from-excel-vba-to-a-script-in-rstudio – M-- May 22 '19 at 21:59

score 0 · Answer 2 · answered May 17 '16 at 14:35

it took a little effort to get this going, but the following worked for me.

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

Make sure you read about setting up the 'permissions' using the link below.

http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html

Error running RScript in Excel VBA

2 Answers2