Running R script using VBA

Question

I'm trying to create a button on Excel that runs an R script I created. This is the VBA code I tried:

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:\R_code\Forecast.R"
errorCode = shell.Run(path, style, waitTillComplete)
End Sub

But that also returns an error -> Run-time error '-2147024894 (80070002) Automation Error.

I much appreciate any help!

Seems like if your R code has a problem then maybe address that first? — Tim Williams
– Tim Williams, Commented Sep 7, 2021 at 17:53
Use a fully qualified path to the executable you want to run. Your current code depends upon the PATH variable and lookup. — HackSlash
– HackSlash, Commented Sep 7, 2021 at 18:07
@HackSlash Can you elaborate on what a fully qualified path is? Or maybe tell me where to find more info about it? Thanks! — Diego
– Diego, Commented Sep 7, 2021 at 18:21

HackSlash · Accepted Answer · 2021-09-07 20:31:37Z

2

The answer was to use a fully qualified path to the executable you want to run in the shell. You can't rely on PATH variable lookups. It's much safer this way.

Sub RunRscript()

    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorCode As Integer
    Dim path As String
    path = """C:\Program Files\R\R-4.1.1\bin\Rscript.exe"" C:\R_code\Forecast.R""

    With CreateObject("WScript.Shell")
        errorCode = .Run(path, style, waitTillComplete)
    End With
End Sub

NOTE: If you need to quickly find the location of an executable in your PATH variable you can run where RScript from cmd.exe and it will tell you the fully qualified path.

answered Sep 7, 2021 at 20:31

HackSlash

6,0282 gold badges28 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

Running R script using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related