Running RScript in VBA

Question

I'm trying to create a VBA script that run RStudio script.

i tried using the Shell() command or the Run oShell at VBA, but the best thing i mange on doing is open the RStudio script, not making it to run automatically.

By the way, the RStudio script create a csv file which i will use. this is the VBA script i use right now:

Sub RunRStudio()

Dim path As String

path = ThisWorkbook.path & "\Test.R"

Set oShell = CreateObject("WScript.Shell")

oShell.Run "RStudio " & path

shell ("RStudio " & path)

End Sub

How can i run this RStudio script automatically from VBA?

Thanks.

Forget about VBA at first. Can you run the script from the command line? Since RStudio is an IDE, I'm not sure why you are trying to use it to launch a noninteractive script. — John Coleman, Apr 15 '17 at 14:22
i tried to run the script with command line with no success. i thought on a solution running a .exe file that runs it but i have little experience with it, but it looks like a good solution — Matan Sheffer, Apr 15 '17 at 14:36
http://stackoverflow.com/questions/18306362/run-r-script-from-command-line/18306656#18306656 — Dason, Apr 15 '17 at 14:50
@Dason i already cross the answer you sent. it does not work at my R 3.3.3, i get an error :package ‘Rscript’ is not available (for R version 3.3.3) — Matan Sheffer, Apr 15 '17 at 15:38
Are you under the impression Rscript is an R package? Reread the answer if that's what you think. Otherwise you just need to make sure either 1) you reference the direct path to Rscript in your VBA or 2) make sure Rscript is on your path (which it probably isn't by default). — Dason, Apr 16 '17 at 16:01

score 1 · Answer 1 · answered Feb 14 '19 at 13:19

I had the same problem. Thank you, because your code helped me to find the solution.

This code, with a little change, it works to open the window of Rstudio, but it doesn't work to run the script. You need to use "RScript", not "RStudio".

You can try this to run your code:

    Sub RunRTest()

    Dim path As String
    path = """C:\Program Files\R\R-3.5.2\bin\RScript.exe"" 
    ""C:\Folder\YourScriptName.R"""

    Set oShell = CreateObject("WScript.Shell")
    oShell.Run path

    End Sub

Or:

    path_file = ThisWorkbook.path & "\YourScriptName.R"
    path = """C:\Program Files\R\R-3.5.2\bin\RScript.exe"" " & path_file & ""

if you have the path of your file in other var.

I hope to help someone!

Running RScript in VBA

1 Answers1