Running R from Excel VBA without RExcel

Question

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.

Although useful, the Excel R wiki doesn't explain how... rwiki.sciviews.org/… — Bob Hopez
– Bob Hopez, Commented Oct 3, 2013 at 22:35
Not sure you need the escaped backslashes in the path to the executable. — Tim Williams
– Tim Williams, Commented Oct 3, 2013 at 22:59
The above VBA code does run the Rscript.exe and R.exe with the above format Tim. For a simple plot to pdf it works fine...I'm trying to get it to work on a more complicated multiple function code block. — Bob Hopez
– Bob Hopez, Commented Oct 3, 2013 at 23:03
Maybe stackoverflow.com/questions/6788928/… would work for you — Tim Williams
– Tim Williams, Commented Oct 4, 2013 at 0:11

bill_080 · Accepted Answer · 2013-10-17 17:24:50Z

3

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.

answered Oct 17, 2013 at 17:24

bill_080

4,7682 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Brewer · Accepted Answer · 2014-06-05 16:49:43Z

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.

Collectives™ on Stack Overflow

Running R from Excel VBA without RExcel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related