-2

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!

lapioche75
  • 87
  • 1
  • 2
  • 9
  • Ciao! Welcome to SO. First of all you should read [here](http://stackoverflow.com/help/how-to-ask) about how to ask a good question; a good question has better likelihood to be solved and you to receive help. On the other hand a read of [this](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) is also good thing. It explains how to create a reproducible example in R. Help users to help you by providing a piece of your data, a desired output and things you have already tried. – SabDeM Oct 31 '16 at 20:21

2 Answers2

4

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.

yeedle
  • 4,918
  • 1
  • 22
  • 22
  • Hi @yeedle, I tried to run the code replacing C:\R_code\hello.R by H:\apps\xp\Desktop\hello.R where my test function is saved but I get the following error: Method 'Run' of Object 'IWshShell3' failed. It looks like this is an issue with running R, any idea? – lapioche75 Oct 31 '16 at 20:57
  • the path to ‘R.exe’ and ‘RScript.exe’ needs to be in your system PATH variable. Is it? – yeedle Oct 31 '16 at 22:32
0

I am not sure this is a direct answer. You might want to look at how it is done using the Bert toolkit. This seems to help a lot in integrating R and Excel with each other.

https://bert-toolkit.com/

ghellquist
  • 195
  • 6