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.

It looks like one problem is the spaces in your file name. Make sure you properly escape them in a way that's appropriate for your operating system so they don't look like separate parameters on the command line. — MrFlick
– MrFlick, Commented Sep 17, 2019 at 13:39

marc_s · Accepted Answer · 2019-10-22 20:15:13Z

6

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.

edited Oct 22, 2019 at 20:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 17, 2019 at 13:51

Louis

3,6222 gold badges13 silver badges20 bronze badges

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

4 Comments

morg Over a year ago

Yes and no it helps haha, it is now looking at the path as one item, but it is still being ignored. 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 of Business Report/Morgan's Trials/Mix of Business Report v1.R' ignored

Louis Over a year ago

Oh, I'm noticing that there's a space in the R.exe path too! Try to insert quotes there.

Louis Over a year ago

Found the solution and tested with my R console! I'll update the answer! ;)

Louis Over a year ago

@morg I've updated the code. With this, you no longer need to use Application.Wait, which may slow down your execution a lot. Check this out. ;)

marc_s · Accepted Answer · 2019-10-22 20:15:07Z

1

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

edited Oct 22, 2019 at 20:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 17, 2019 at 15:20

Parfait

108k19 gold badges103 silver badges138 bronze badges

2 Comments

morg Over a year ago

Hi! It seems to run through fine (ie not throwing an error) however, I don't think it actually runs the script, as at the end of it, the script has code that runs a different macro in the same file. When I source the script by itself (not prompted by VBA) it runs the macro fine, but when I run the original macro that you have helped me set up, then it doesn't

Parfait Over a year ago

Please open Powershell and run the command that outputs from Debug.Print. Be sure to check file paths and/or versions. Right now, I cannot verify your claims but syntax indicates it should work. Possibly, the R script run in VBA errors out for some reason. In R, be sure to call absolute paths or use set working directory.

Collectives™ on Stack Overflow

R ignoring R script when excecuting through VBA

2 Answers 2

Edit

Sync Method

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

Sync Method

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related