CODEWITHSUNDEEP
rexcelvba
Diego
Diego

Reputation: 93

Running R script using VBA

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!

Upvotes: 1

Views: 656

Answers (1)

HackSlash
HackSlash

Reputation: 5805

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.

Upvotes: 2

Related Questions