Running R script in Excel using Shell command in VBA

Question

I am using some code I took from here; Run R function in VBA macro

Sub RunRscript()
'runs an external R code through Shell
'The location of the RScript is 'C:/Users/abhishek.b.jai/Downloads/data'
'The script name is 'testcode.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 = "C:/Users/abhishek.b.jai/Downloads/data/testcode.r"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

I'm also using the same code itself but getting the error: Run-time error 429 mentioning "ActiveX component can't create object". Please help me with this issue. Thank You!

check this answer : https://stackoverflow.com/questions/18306362/run-r-script-from-command-line — moodymudskipper, May 31 '17 at 07:53
the path of the script is not your entire command, you have to precede it with `Rscript` or `R CMD BATCH` — moodymudskipper, May 31 '17 at 07:55
Code: Public Sub RunRTest() shell ("""C:\Users\abhishek.b.jai\Documents\R\R- 3.4.0\bin\RScript"" C:\Users\abhishek.b.jai\Downloads\data\testcode.R") End Sub similar code is running but nothing happens in R. — Abhishek kumar Jain, May 31 '17 at 08:23
What should your testcode script do ? What do you want to happen ? Do I understand correctly that your last piece of code doesn't return an error ? — moodymudskipper, May 31 '17 at 10:03
Yes. I just want to run a Rscript from Excel itself using the VBA. — Abhishek kumar Jain, May 31 '17 at 10:42

score 0 · Accepted Answer · answered May 31 '17 at 17:29

You should probably take a look at this.

http://rcom.univie.ac.at/download.html

Also, consider doing it this way.

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

Look closely at the path...

https://www.howtogeek.com/181774/why-windows-uses-backslashes-and-everything-else-uses-forward-slashes/

Running R script in Excel using Shell command in VBA

1 Answers1