Running R scripts from VBA

Public Sub RunRTest()  Shell ("Rscript test.r")End Sub

Note be careful with your file locations and may need more explicit Shell dim statements....e.g. replace with these lines in your VB

Dim shell As Object   Set shell = VBA.CreateObject("WScript.Shell")   Dim waitTillComplete As Boolean: waitTillComplete = True   Dim style As Integer: style = 1   Dim errorCode As Integer   Dim  path As Stringpath = """" & Cells.Range("RhomeDir") & """ """ & Cells.Range("MyRscript") & """"errorCode = shell.Run(path, style, waitTillComplete)

where, in Excel a cell with a named reference RhomeDir contains text

C:\Program Files\R\R-3.2.3\bin\x64\rscript and

MyRscript contains text C:/Documents/Rworkings/Rscripttest.s

noting the unix R backslash and .s or .r postfix and VB replaces "" with " to give double brackets in path expression (plus further outside brackets to denote string). Also not a good idea to have spaces in your file name.

The full dim syntax of the shell command above was found by searching for VBA shell.

I put everything in a function that can be called easily. The output is the shell.run output, which is an integer:

Function to Run an R Script:

Function Run_R_Script(sRApplicationPath As String, _                        sRFilePath As String, _                        Optional iStyle As Integer = 1, _                        Optional bWaitTillComplete As Boolean = True) As Integer    Dim sPath As String    Dim shell As Object    'Define shell object    Set shell = VBA.CreateObject("WScript.Shell")    'Wrap the R path with double quotations    sPath = """" & sRApplicationPath & """"    sPath = sPath & " "    sPath = sPath & sRFilePath    Run_R_Script = shell.Run(sPath, iStyle, bWaitTillComplete)End Function

Examples how to call:

Sub Demo()    Dim iEerrorCode As Integer    iEerrorCode = Run_R_Script("C:\Program Files\R\R-3.4.4\bin\x64\rscript","C:\Ibos\R\WF_Metrics\Abe.R")End Sub

OR

Sub Demo()    Dim iEerrorCode As Integer    Dim WS as WorkSheet    Set WS=ThisWorkBook.Worksheets("Sheet1")    iEerrorCode = Run_R_Script(WS.Range("A1"),WS.Range("A2")) 'cell A1=adderess of R application and cell A2 is the address of your R file, one can use a named range tooEnd Sub