CODEWITHSUNDEEP
rexcelvba
ninja112
ninja112

Reputation: 23

Why does VBA code open R file instead of running code

I have a VBA macro in excel that calls an R code. The VBA button only opens the R file instead of also running the code. Here is the code:

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

The R code eventually creates a new excel document once it is run. I want the button to run the R code and create this new document. If I just run the R code separately it works fine. I am wondering what I need to add to this code so the R file actually runs instead of only opening.

Upvotes: 1

Views: 256

Answers (1)

Oliver
Oliver

Reputation: 8602

VBA.CreateObject("WScript.Shell").run(...)

Will basically run whatever is given in the ... segment through the windows command prompt. This is equivalent to running a batch or powershell script. (pressing start, typing cmd and running code through the command prompt)

By default each type of document has some default ways to open. Folders, files etc. often open in some viewer. Giving the directory to an R file will perform the same action as double clicking it.

In order to run it as a script, you will need to open it through R's executable Rscript.exe. Default directory is given in 

C:\Program Files\R\R-3.5.1\bin

Most often this is done by creating an environment variable to this directory, at which point you can run the rscript through

path = "Rscript [fullpath to R file] [Arguments]"
VBA.CreateObject("WScript.Shell").run(path)

For reference how to set this path please check out java's great guide here: How do I set or change the PATH system variable?

Assuming you want to avoid this, you could simply run the script by directly refering to the fullpath for the 'rscript' executable

path = """C:\Program Files\R\R-3.5.1\bin\Rscript.exe"" ""C:\Users\user\Documents\Forecast.R"""
VBA.CreateObject("WScript.Shell").run(path)

The double quotes seem necessary in this case.

Upvotes: 1

Related Questions