I'm trying to run a R code from excel VBA macro.
The R function is using two inputs: DataFilter(Input1,Input2) The function does some filtering and then saves a new excel file in the folder N:\DataFolder
Can you please help in creating an VBA macro that runs the DataFilter function with two new text inputs (lets say cells A1 and B1 from the sheet1 in the spreadsheet). From there I will be able to open the excel file in the vba macro.
Thank you!
Answer
You can run an R script in VBA by creating Windows Shell obejct and passing it a string that executes an R script
Sub RunRscript()
'runs an external R code through Shell
'The location of the RScript is 'C:\R_code'
'The script name is 'hello.R'
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 String
path = "RScript C:\R_code\hello.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub
(source)
You can pass in your cell values as command line args to the R script. See ?commandArgs for more.