Running R Scripts from Vba

Running R scripts from VBA

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

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

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.

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 Script execution from inside VBA using Shell not working

Running R from VBA is an annoying endeavor. If you want R code to run directly from VBA, i suggest looking into BERT, which is an open source add-in for Excel, letting you run and write your R code directly from excel, making debugging your code much much simpler.

That said if you are stuck with shell.run, there are some things you can do to locate your problem.

Manual debugging

In VBA either set a breakpoint or print your path to your console.

path = """C:\Program Files\R\R-3.4.4\bin\x64\RScript"" C:\Ibos\R\WF_Metrics\abc.R"
debug.print path

Open your command prompt (press the windows button and write cmd, press enter start, cmd, enter.)

Paste the line of code into your command prompt. Any error will be printed into the command line, and from this you can locate and correct the error in your script.

Less manual debugging

Now the manual debugging can be tedious and as i note below it is system specific. There are several options to automate the process slightly. These include:

1. Read the error code directly into VBA, pipe in any output to VBA.
2. Use an open source integration like BERT to let you write and debug your code somewhat directly in Excel.
3. Use System error messages to identify the error

The first option is the more complicated, but also a very versatile and nice option. I suggest the first answer here, which gives a link to a VBA module that can achieve this method. Note however it is a 32 bit module, and it will need a few ptrsafe markers, for the windows api to work on a 64 bit installation of excel. With a few changes it could even be used to read text output (data.frame etc) from R directly, with minimal interfering with Excel.

For the second option i suggest looking at the BERT webpage, which provides good guides for utilizing the implementation. This has the disadvantage that any computer will need to have installed BERT for your excel script to work, in addition to R being installed.

The third option is one inspired by Chip Pearson's site. When your script crashes it sends an error code to the command line, and this can be interpreted by the windows error code manager. This has the advantage that it is simple, and you will quickly be made aware if your script 'does not exist' or similar common mistakes that are not R specific.

Using this method one would change the R execution script to something like

Sub RunRScript()
    Dim shell As Object

    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorCode As Integer
    Dim path As String
    Set shell = VBA.CreateObject("WScript.Shell")

    path = """C:\Program Files\R\R-3.4.4\bin\x64\RScript"" C:\Ibos\R\WF_Metrics\abc.R"

    errorCode = shell.Run(path, style, waitTillComplete)
    if errorCode <> 0 then
        errorString = GetSystemErrorMessageText(errorCode)
        Err.Raise errorCode, "Run_R_Script", errorString
    end if 
End Sub

where GetSystemErrorMessageText(errorCode) is a call to the function in a seperate module below.

#If Win64 Then
    Private Declare PtrSafe Function FormatMessage Lib "kernel32" Alias "FormatMessageA" ( _
        ByVal dwFlags As Long, _
        ByVal lpSource As Any, _
        ByVal dwMessageId As Long, _
        ByVal dwLanguageId As Long, _
        ByVal lpBuffer As String, _
        ByVal nSize As Long, _
        ByRef Arguments As Long) As Long
#Else
    Private Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" ( _
        ByVal dwFlags As Long, _
        ByVal lpSource As Any, _
        ByVal dwMessageId As Long, _
        ByVal dwLanguageId As Long, _
        ByVal lpBuffer As String, _
        ByVal nSize As Long, _
        ByRef Arguments As Long) As Long
#End If
Public Function GetSystemErrorMessageText(ErrorNumber As Long) As String
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' GetSystemErrorMessageText
    '
    ' This function gets the system error message text that corresponds
    ' to the error code parameter ErrorNumber. This value is the value returned
    ' by Err.LastDLLError or by GetLastError, or occasionally as the returned
    ' result of a Windows API function.
    '
    ' These are NOT the error numbers returned by Err.Number (for these
    ' errors, use Err.Description to get the description of the error).
    '
    ' In general, you should use Err.LastDllError rather than GetLastError
    ' because under some circumstances the value of GetLastError will be
    ' reset to 0 before the value is returned to VBA. Err.LastDllError will
    ' always reliably return the last error number raised in an API function.
    '
    ' The function returns vbNullString is an error occurred or if there is
    ' no error text for the specified error number.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ErrorText As String
    Dim TextLen As Long
    Dim FormatMessageResult As Long
    Dim LangID As Long
    ''''''''''''''''''''''''''''''''
    ' Initialize the variables
    ''''''''''''''''''''''''''''''''
    LangID = 0&   ' Default language
    ErrorText = String$(FORMAT_MESSAGE_TEXT_LEN, vbNullChar)
    TextLen = FORMAT_MESSAGE_TEXT_LEN
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Call FormatMessage to get the text of the error message text
    ' associated with ErrorNumber.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    FormatMessageResult = FormatMessage( _
                            dwFlags:=FORMAT_MESSAGE_FROM_SYSTEM Or _
                                     FORMAT_MESSAGE_IGNORE_INSERTS, _
                            lpSource:=0&, _
                            dwMessageId:=ErrorNumber, _
                            dwLanguageId:=LangID, _
                            lpBuffer:=ErrorText, _
                            nSize:=TextLen, _
                            Arguments:=0&)
    If FormatMessageResult = 0& Then
        ''''''''''''''''''''''''''''''''''''''''''''''''''
        ' An error occured. Display the error number, but
        ' don't call GetSystemErrorMessageText to get the
        ' text, which would likely cause the error again,
        ' getting us into a loop.
        ''''''''''''''''''''''''''''''''''''''''''''''''''
        MsgBox "An error occurred with the FormatMessage" & _
               " API function call." & vbCrLf & _
               "Error: " & CStr(Err.LastDllError) & _
               " Hex(" & Hex(Err.LastDllError) & ")."
        GetSystemErrorMessageText = "An internal system error occurred with the" & vbCrLf & _
            "FormatMessage API function: " & CStr(Err.LastDllError) & ". No futher information" & vbCrLf & _
            "is available."
        Exit Function
    End If
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' If FormatMessageResult is not zero, it is the number
    ' of characters placed in the ErrorText variable.
    ' Take the left FormatMessageResult characters and
    ' return that text.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ErrorText = Left$(ErrorText, FormatMessageResult)
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Get rid of the trailing vbCrLf, if present.
    '''''''''''''''''''''''''''''''''''''''''''''
    If Len(ErrorText) >= 2 Then
        If Right$(ErrorText, 2) = vbCrLf Then
            ErrorText = Left$(ErrorText, Len(ErrorText) - 2)
        End If
    End If
    ''''''''''''''''''''''''''''''''
    ' Return the error text as the
    ' result.
    ''''''''''''''''''''''''''''''''
    GetSystemErrorMessageText = ErrorText
End Function

Credit goes to Chip Pearson, although it was likely not intended for this use.
Executing shell.run(path) where path is a command to execute an R script, will now return an error message if it fails.
This does not remove manual debugging in its entirety, but the error message will help you identify errors outside of R, and often gives valuable error descriptions that will help you identify the error faster during manual debugging.

This method should thus be followed by manual debugging to the degree it is necessary.

Notes:

1. Running R scripts from your command line is system encoding specifically. Saving your R scripts using 'Save with encoding' and using 'System.locale' removed most of our problems when running from the command line. It might fail on UTF8 characters, however, which is a whole other hassle.
2. The third option is the simplest, and therefore also the least versatile. Error codes from the windows system only gives a glance into your problem and does not specify which line failed, only that Invalid function or similar was called. There is no guarantee that this error will be exactly the correct one. It does, however, give you most common mistakes like wrong paths, functions simply not working, variables called that do not exist etc.

I hope this will give some clarity and help you find your error. I suggest searching on google, the topic of using shell.run for various integrations is a topic that has been investigated and it if better options exist it is often recommended to be avoided (due to the limitations). It is however often a good place to start. Many sites like this one, shows how one can use the output from R in VBA, by saving your output to text files in R and reading from VBA. Better options do exist, but this is likely the simplest method.

Update by the Asker:
After a lot of investigation I realized the following points:

1- xlsx package uses rJava package and you need to install Java first

2- Java's 32-bit or 64-bit version has impacts on whether the rJava package and subsequently xlsx package can be loaded successfully or not. I recommend installing a 64-bit version of everything (Excel, R, Java) if you have a 64-bit windows OS.

3- It seems by default R is installed both 32-bit and 64-bit version so you have to specify which version you want to use. Choose the version here on RStudio:

Tools > Global Options > R Version

mine by default was 32-bit and Library(rJava) was throwing the error even on RStudio after I installed a new version of Java. I changed it to 64-bit and it worked.

You can use R scripts to do this too, but it seems if you run this code from inside RStudion it won't work since you need to manually change the R Version and close RStudion and launch it again to take effect

Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre7') # for 64-bit version
Sys.setenv(JAVA_HOME='C:\\Program Files (x86)\\Java\\jre7') # for 32-bit version
library(rJava)

4- I noticed that I have both 32-bit and 64-bit Java installed on my machine. Once I did JAVA_HOME to the environment variables, it caused a jvm.dll missing error so I deleted it and everything came back to working fine again

5- I saw in some of the post if you use another alternative for xlsx package you would not need to go through all of the hassle of making sure everything is working fine together.

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.

---

Related Topics