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.

Please refer to the following link: stackoverflow.com/questions/19404270/run-vba-script-from-r — Reza Sadeghi
Hi Reza, Thank you for your suggestion. I can't think of any way in which that helps though. Basically, I need to be able to call the R script and pass the argument from Excel/VBA, without the user needing to be required to open R / Rstudio manually — LiviusI
Maybe if I elaborate on the purpose of my code a bit more, it would make more sense: I have an excel front-end which contains a list of filepaths. These are inputted manually by the user. Then, they press a button to launch an R script that will use those file paths to read the files into R and do a series of operations on them. The argument that I want to pass to the R script is the file path of the Excel workbook itself, as this can change from time to time and will be used by numerous users. So I want R to be able to know the location of the workbook and open autom. Hope this makes sense — LiviusI

HackSlash HackSlash · Accepted Answer · 2018-07-02T16:30:37

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

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

1 Answers