How to pass an argument from Excel/VBA to a script in Rstudio

Question

I am trying to open a R script with Rstudio from VBA, while at the same time passing an argument to the R script, that I can then access with commandArgs().

The problem is very similar to the one described here:

WScript.Shell to run a script with spaces in path and arguments from VBA

However, the solution, albeit very good, does not seem to work for me.

Here is the VBA code I am using:

Sub RunRscript()

    ActiveWorkbook.Save

    Dim shell               As Object
        waitTillComplete    As Boolean, _ 
        style               As Integer, _ 
        errorcode           As Integer, _ 
        path                As String, _ 
        var1                As String

    Set shell = VBA.CreateObject("WScript.Shell")
    waitTillComplete = True
    style = 1
    var1 = Range("F3").Value
    path = Chr(34) & "C:\Program Files\RStudio\bin\rstudio.exe" & Chr(34) & " " & 
    Chr(34) & "C:\Users\LI\Downloads\starting_code_v3.R"
    '& Chr(34) & " " & Chr(34) & "var1" & Chr(34)

    errorcode = shell.Run(path, style, waitTillComplete)

End Sub

As you can see, I am using almost the same code as in the link provided above, with a few additions. Namely, I define var1 as the contents of the cell F3 (in my case, the cell contains the path of a file, but I suppose it could be anything).

Now, if I run the code as presented above, it works and it launches RStudio and opens the script. However, if I add the code commented out 1 line below to the variable (i.e. if I try to launch the script while at the same time passing the argument var1), RStudio opens, but the script doesn't, nor is any value passed on.

Any suggestion would be helpful.

Please note that I have looked in every possible similar topic on stackoverflow and on google, as well as trying loads of combinations of quotes and double quotes. ( I am not very proficient in VBA).

Please also note:

• Running Win7
• Running RStudio 1.1.383 & R 3.4.2
• I do not want to use cmd or other tools. This needs to run from an excel workbook through a button that links to the vba code - as it will be used by complete newbies.
• I do not want to use RScript at the moment; maybe I will consider that after finishing all the code in RStudio. But for now, I need to be able to read the parameter in RStudio through commandArgs and do some manipulation.

Please if possible offer your advice taking into account the above.

I hope all of the above makes sense, otherwise please ask for clarification.

Thank you in advance.

Answer 1

EDIT:

The problem is RStudio and not your code. RStudio doesn't accept command line arguments:

https://support.rstudio.com/hc/en-us/community/posts/200659066-Accessing-command-line-options-in-RStudio

OLD ANSWER:

The problem I am seeing is that you are putting the text "var1" in to the path instead of the contents of the variable called var1. I replaced your Chr(34) with stacked quotes because it's easier for me to keep track of. I apologize if this looks less readable to you. I tested the string and it does feed the contents of Var1 as a command line argument.

Try this:

Option Explicit

Public Sub RunRscript()
    ActiveWorkbook.Save
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Long: style = 1
    Dim errorcode As Long
    Dim path As String
    Dim var1 As String
    var1 = ActiveSheet.Range("F3").Value
    path = """C:\Program Files\RStudio\bin\rstudio.exe"" ""C:\Users\LI\Downloads\starting_code_v3.R"" """ & var1 & """"

    errorcode = shell.Run(path, style, waitTillComplete)
End Sub