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... http://rwiki.sciviews.org/doku.php?id=tips%3adata-io%3ams_windows&s=excel — Bob Hopez, Oct 03 '13 at 22:35
Not sure you need the escaped backslashes in the path to the executable. — Tim Williams, Oct 03 '13 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, Oct 03 '13 at 23:03
Maybe http://stackoverflow.com/questions/6788928/how-to-run-a-r-language-r-file-using-batch-file would work for you — Tim Williams, Oct 04 '13 at 00:11

score 3 · Answer 1 · answered Oct 17 '13 at 17:24

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.

score 1 · Answer 2 · answered Jun 05 '14 at 16:49

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.

Running R from Excel VBA without RExcel

2 Answers2

Linked