CODEWITHSUNDEEP
rvbaexcel
Bob Hopez
Bob Hopez

Reputation: 783

Running R from Excel VBA without RExcel

Can this process be simplified?

First, I manually open this file in R: C:\R\ExampleModel\ModelScript.R

From R-Editor, when the code below is run from the open ModelScript.R file, it processes the Model.R script correctly. 

source("C:\\R\\ExampleModel\\Model.R", echo=T)

Within Excel, I want to run the source code above without manually opening ModelScript.R from R first. Is there anything I can modify in the VBA code below to process the source() command automatically from Excel/VBA? If a batch process is the only option, short of Rexcel, please use the example extensions provided.

Excel 2007 VBA code:

Sub RRUN()

    Dim rCommand As String
    rCommand = "C:\\Program Files\\R\\R-3.0.0\\bin\\Rscript.exe --verbose C:\\R\\ExampleModel\\ModelScript.R"

    'Timer Set to run full Model.R script
    Application.Wait Now + TimeValue("00:00:05")

    'Runs R Script and Arguements into process
    Shell rCommand, vbNormalFocus

    'Timer Set to run full Model.R Script
    Application.Wait Now + TimeValue("00:00:05")

End Sub

Note: I tried using R.exe in place of Rscript.exe above, with no results.

Upvotes: 2

Views: 18091

Answers (2)

Charles Brewer
Charles Brewer

Reputation: 183

The following appears to work:

Make sure that rscript.exe is in your system PATH

Make sure that your inverted commas in the path= statement work for the filenames. I have left it exactly as the one I now have running rather than make it generic (and more confusing). 

Sub RunRscript()
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:\Users\Charles\SkyDrive\Documents\Development\G4H\Technical evaluation templates\Graphical analysis.R"""
errorCode = shell.Run(path, style, waitTillComplete)

End Sub

This was put together using code from http://shashiasrblog.blogspot.co.uk/2013/10/vba-front-end-for-r.html, and with advice from MrFlick. From what I can see it should also be pretty generic.

Upvotes: 1

bill_080
bill_080

Reputation: 4760

You might want to look into Visual Studio Tools for Office. For Excel 2007, the VSTO link is here.

Here's an example of R within Excel (written using VTSO code). Look at the section titled "Other Applications". Here's another example from the same people. I have no idea if they sell those applications, or if everything is custom made.

Here's another possible solution. I haven't pursued this method because my application has very specific requirements.

My point is, even though you can put something together using VBA, it looks like there are better schemes.

Upvotes: 3

Related Questions