Run R Script in Excel

Run R script in Excel

It seems quite odd that it is opening in RStudio. I would suggest running it straight through R.exe. It looks like the PATH is setup all correctly from what you have told us. So you can call R.exe like this if don't need the output:

Sub RunRscript()
    Shell ("R CMD BATCH C:\R_code\hello.R")
End Sub

If you need the output then you'll need to make a WshShell object like this:

Sub RunRscript()
    Dim output As String
    output = CreateObject("WScript.Shell").Exec("R CMD BATCH C:\R_code\hello.R").StdOut.ReadAll
End Sub

This is the older way to run R scripts but should work fine for the time being. You may want to look into your installation of R a bit more to see if there are any other problems.

Run R script from VBA

Finally, here is a solution working well:

Function Run_R_Script(sRApplicationPath As String, _
                    sRFilePath As String, _
                    Optional iStyle As Integer = 1, _
                    Optional bWaitTillComplete As Boolean = True) As Integer

Dim sPath As String
Dim shell As Object

'Define shell object
Set shell = VBA.CreateObject("WScript.Shell")

'Wrap the R path with double quotations
sPath = """" & sRApplicationPath & """"
sPath = sPath & " "
sPath = sPath & sRFilePath

Run_R_Script = shell.Run(sPath, iStyle, bWaitTillComplete)
End Function

Sub Demo()
Dim iEerrorCode As Integer
iEerrorCode = Run_R_Script("C:\Program Files\R\R-3.6.1\bin\x64\Rscript", 
"C:\Users\myname\Desktop\Code.R")
End Sub

Running R script using VBA

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.

Running R script in Excel using Shell command in VBA

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/

R ignoring R script when excecuting through VBA

It seems like the problem is in the path, because in UNC you need to put quotes in a path that contains spaces. Change the command like this and it will work:

Dim rCommand As String
rCommand = """C:\Program Files\R\R-3.5.3\bin\R.exe"" --verbose ""U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"""

Edit

Another problem was that R.exe shouldn't be used to simply execute scripts. We can use RScript.exe for that purpose and we can avoid the console closing, by adding && pause at the end of the command. Here's the complete code:

    Dim rCommand As String
    rCommand = """C:\Program Files\R\R-3.5.3\bin\RScript.exe"" --verbose ""U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"" && pause"

    'Timer Set to run full Model.R script
    Application.Wait Now + TimeValue("00:01:05")

    'Runs R Script and Arguments into process
    Shell rCommand, vbNormalFocus

    'Timer Set to run full Model.R Script
    Application.Wait Now + TimeValue("00:01:05")

Sync Method

A way to improve the function is to execute the shell with waitTillComplete flag, which executes the command with a Sync call. Here's the code:

Sub R_Exec()
    Dim cmd As Object
    Dim rCommand As String, rBin As String, rScript As String
    Dim errorCode As Integer
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim debugging As Boolean: debugging = True
    Set cmd = VBA.CreateObject("WScript.Shell")

    rBin = """C:\Program Files\R\R-3.5.3\bin\RScript.exe"""
    rScript = """U:\Reporting\Mix of Business Report\Morgan's Trials\Mix of Business Report v1.R"""

    'Check if the shell has to keep CMD window or not
    If debugging Then
        rCommand = "cmd.exe ""/k"" " & rBin & " " & rScript
    Else
        rCommand = rBin & " " & rScript
    End If

    Debug.Print rCommand 'Print the command for debug

    'Runs R Script and Arguments into process, returning errorCode
    errorCode = cmd.Run(rCommand, vbNormalFocus, waitTillComplete)
End Sub

With this Sub you can decide if keep the shell window open or not.

Hope this helps.

Running R scripts from VBA

Public Sub RunRTest()
  Shell ("Rscript test.r")
End Sub

---

Related Topics