Windows Script Host fails

2
votes

I am trying to run a R script from Excel using VBA. Ideally, I would use an .R (Rscript) name to invoke R and run the process, or if that won't go, then invoke Rscript.exe and execute the filename passed to it.

REXcel is no good because it requires a 32-bit version of Excel (and I am not working in 1989)

I have found what seems to be a perfect script at (http://shashiasrblog.blogspot.co.uk/2013/10/vba-front-end-for-r.html) which, after suitable localisation looks like this:

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

This fails with the message

Run Time Error '-2147024894 (80070002)': Method 'Run' of object 'IWshShell3' failed.

Which tell me exactly nothing. I have tried Googling the error message but got nothing.

I have set the PATH variable to include the directory where R and Rscript live.

I suspect it is something straightforward, but there sems to be a lack of simple ways of executing R from Excel.

3
rexcelvbawsh
What does "C:\Users\Charles\SkyDrive\Documents\Development\G4H\Technical evaluation templates\Graphical analysis.R" look like? Normally you would use RScript to help run a script. Do you reference that exe at all? – MrFlick
It's a fairly long R script for producing a variety of standard graphics. I am trying to put together a little suite of programs which allow data to be collected in Excel (because most people have it), then to export a summary of the data for processing in R (because its graphics are so much better than Excel). I am looking at automating the R process by calling it from Excel and a bit of VBA seemed like the obvious route. – Charles Brewer
Can you run it from the command line just fine? If so, how do you run it. I don't think you can just "run" an R file like that. Usually you have something like Rscript.exe scriptname.R – MrFlick
Yes, running it from the command line works perfectly. The CL version was: rscript "C:\users\Charles ... \Graphical analysis.R" This ran beautifully (to my surprise), so it was exactly as you have shown. I am assuming that there must be some nice simple way of kicking it off from inside Excel using VBA (and not a million miles from the original script). – Charles Brewer

3 Answers

3
votes

You need to add Rscript to your path, otherwise the shell doesn't know what program to send the file to. So modify the path to be

path = "rscript C:\Users\Charles\SkyDrive\Documents\Development\G4H\Technical evaluation templates\Graphical analysis.R"

You may have to provide the fill path to rscript depending on whether or not that directory is in your search path.

1
votes

I am not sure if this is correct protocol (no doubt I shall receive some horrendous penalty), but thanks to Mr Flick and some poking about I have a solution:

  1. Make sure that rscript.exe is in the system path.

  2. Try to work out how many inverted commas are required to actually get Windows to understand that it is a directory.

  3. Then:

    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)

The horrible parsing of cmd when handling white space in directory names finally succumbed to a brute force attack!

1
votes

I had the same problem. Change the code to include path for both Rscript.exe and R file. The following code worked for me:

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, p1, p2 As String
path = "RScript E:\R_Folder\VBA_R.R"
p1 = "E:\R-3.1.2\bin\x64"
p2 = "E:\R_Folder"
errorCode = shell.Run("""" & p1 & "\Rscript.exe"" /filename """ & p2 & "\VBA_R.R"" /delay 10000 /preview /quiet", style, waitTillComplete)
'errorCode = shell.Run(path, style, waitTillComplete)
End Sub