Problem running Rscript in Excel 2013 through shell

Question

I have been successfully running my r script in Excel 2010 & Windows 7 using vba for a long time. Then I had to use another computer which has Excel 2013 & Windows 8 installed. It gets me the following error:

Method 'Run' of object 'IWshShell3' failed

This is the code that runs in Excel 2010:

Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Integer: style = 0
Dim errorCode As Integer
Dim var1 As String
Dim PATH As String

var1 = Replace(Replace(ThisWorkbook.PATH, "\", "/") & "/", " ", "_z9z_")

PATH = "C:\PROGRA~1\R_App\bin\x64\RScript """ & "C:\JF\Code A\dvlp.R"" " & var1 & " " & 2500

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

This same code doesn't run in the other computer I mentioned.

I have gone through other questions in stackoverflow stating the same issue, but the solutions there hasn't helped me. For example, I already use double quotes and I have tried getting rid of them.

Any ideas?

score 0 · Answer 1 · answered Oct 14 '19 at 19:46

Consider a cleaner build of command line Rscript call that can handle spaces in names which may be the issue here. Below also integrates proper error handling.

Sub Run_R_Script()
On Error Goto ErrHandle
    Dim shell As Object
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 0
    Dim errorCode As Integer

    Dim args(0 To 3) As String
    Dim rCommand As String
    Dim i As Integer

    args(0) = "C:\PROGRA~1\R_App\bin\x64\RScript"
    'args(0) = "Rscript.exe"                          ' IF R BIN FOLDER IN PATH ENV VARIABLE
    args(1) = "C:\JF\Code A\dvlp.R"
    args(2) = Replace(Replace(ThisWorkbook.PATH, "\", "/") & "/", " ", "_z9z_")
    args(3) = 2500

    rCommand = args(0)
    For i = 1 To UBound(args)
        rCommand = rCommand & " """ & args(i) & """"
    Next i        
    Debug.Print rCommand                              ' CHECK COMMAND LINE CALL

    Set shell = VBA.CreateObject("WScript.Shell")
    errorCode = shell.Run(rCommand, style, waitTillComplete)

ExitHandle:
    Set shell = Nothing                               ' RELEASE ALL set OBJECTS
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    Resume ExitHandle
End Sub

What is the error from above error handler? Please provide specific error number. Also, can you set up a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/59654510) of sample script that takes your two arguments (i.e., path and number)? I cannot see your `ThisWorkbook.PATH`. Also, post the `Debug.Print` of command as included in solution. — Parfait, Oct 14 '19 at 20:42

Problem running Rscript in Excel 2013 through shell

1 Answers1