R ignoring R script when excecuting through VBA

Question

I am trying to run a R script from excel VBA. The script just queries data, formats and organizes it, and then exports it as a separate XLS file. However, I can not get the R script to run.

Going off of this post: (albeit the OG code is what I understood) Running R from Excel VBA without RExcel I don't have access to RExcel due to work restrictions on downloads. I have found my R.exe file paths and the macro below is able to run:

 Dim rCommand As String
rCommand = "C:\Program Files\R\R-3.5.3\bin\R.exe --verbose U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"

'Timer Set to run full Model.R script
Application.Wait Now + TimeValue("00:01:05")

'Runs R Script and Arguments into process
shell rCommand, vbNormalFocus

'Timer Set to run full Model.R Script
Application.Wait Now + TimeValue("00:01:05")

I wanted my macro to run in the background, however the R window (NOT Rstudio, which is fine, as long as it works) doesn't run the R script.

Here is what I am shown in the "Rterm" window:

CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

ARGUMENT 'U:\Reporting\Mix' __ ignored__

ARGUMENT 'of' __ ignored__

ARGUMENT 'Business' __ ignored__

ARGUMENT 'Report\Morgan's' __ ignored__

ARGUMENT 'Trials\Mix' __ ignored__

ARGUMENT 'of' __ ignored__

ARGUMENT 'Business' __ ignored__

ARGUMENT 'Report' __ ignored__

ARGUMENT 'v1.R' __ ignored__

'verbose' and 'quietly' are both true; being verbose then ..
now dyn.load("C:/Program Files/R/R-3.5.3/library/methods/libs/x64/methods.dll") ...

'verbose' and 'quietly' are both true; being verbose then ..
'verbose' and 'quietly' are both true; being verbose then ..
now dyn.load("C:/Program Files/R/R-3.5.3/library/utils/libs/x64/utils.dll") ...
'verbose' and 'quietly' are both true; being verbose then ..
Garbage collection 1 = 0+0+1 (level 2) ...
11.5 Mbytes of cons cells used (35%)
2.7 Mbytes of vectors used (4%)
now dyn.load("C:/Program Files/R/R-3.5.3/library/grDevices/libs/x64/grDevices.dll") ...
'verbose' and 'quietly' are both true; being verbose then ..
now dyn.load("C:/Program Files/R/R-3.5.3/library/graphics/libs/x64/graphics.dll") ...
'verbose' and 'quietly' are both true; being verbose then ..
now dyn.load("C:/Program Files/R/R-3.5.3/library/stats/libs/x64/stats.dll") ...
 ending setup_Rmainloop(): R_Interactive = 1 {main.c}
 >R_ReplConsole(): before "for(;;)" {main.c}

Why is my file path being ignored? I also tried changing the backslashes of my verbose r script path to forward slashes, same message.

Sorry if it's repetitive, I don't understand any of the other questions posted and the one linked above was my best shot.

Answer 1

It seems like the problem is in the path, because in UNC you need to put quotes in a path that contains spaces. Change the command like this and it will work:

Dim rCommand As String
rCommand = """C:\Program Files\R\R-3.5.3\bin\R.exe"" --verbose ""U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"""

Edit

Another problem was that R.exe shouldn't be used to simply execute scripts. We can use RScript.exe for that purpose and we can avoid the console closing, by adding && pause at the end of the command. Here's the complete code:

    Dim rCommand As String
    rCommand = """C:\Program Files\R\R-3.5.3\bin\RScript.exe"" --verbose ""U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"" && pause"

    'Timer Set to run full Model.R script
    Application.Wait Now + TimeValue("00:01:05")

    'Runs R Script and Arguments into process
    Shell rCommand, vbNormalFocus

    'Timer Set to run full Model.R Script
    Application.Wait Now + TimeValue("00:01:05")

Sync Method

A way to improve the function is to execute the shell with waitTillComplete flag, which executes the command with a Sync call. Here's the code:

Sub R_Exec()
    Dim cmd As Object
    Dim rCommand As String, rBin As String, rScript As String
    Dim errorCode As Integer
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim debugging As Boolean: debugging = True
    Set cmd = VBA.CreateObject("WScript.Shell")

    rBin = """C:\Program Files\R\R-3.5.3\bin\RScript.exe"""
    rScript = """U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"""

    'Check if the shell has to keep CMD window or not
    If debugging Then
        rCommand = "cmd.exe ""/k"" " & rBin & " " & rScript
    Else
        rCommand = rBin & " " & rScript
    End If

    Debug.Print rCommand 'Print the command for debug

    'Runs R Script and Arguments into process, returning errorCode
    errorCode = cmd.Run(rCommand, vbNormalFocus, waitTillComplete)
End Sub

With this Sub you can decide if keep the shell window open or not.

Hope this helps.

Answer 2

Alternatively, for cleaner code to avoid too many quote handling and extendable to many arguments consider building command line call from an array of arguments (borrowed from @UriGoren). Also, use CMD.exe /k to launch window of the automated Rscript (which ideally should be accessible through the PATH environment variable):

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

    args(0) = "CMD.exe"
    args(1) = "/k"
    'args(2) = "Rscript.exe"                          ' IF R BIN FOLDER IN PATH ENV VARIABLE
    args(2) = "C:\Program Files\R\R-3.5.3\bin\RScript.exe"
    args(3) = "U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"

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

    'Timer Set to run full Model.R script
    Application.Wait Now + TimeValue("00:01:05")

    'Runs R Script and arguments into process
    Shell rCommand, vbNormalFocus

    'Timer Set to run full Model.R Script
    Application.Wait Now + TimeValue("00:01:05")
End Sub