CODEWITHSUNDEEP
rvbashell
Ibo
Ibo

Reputation: 4329

R Script execution from inside VBA using Shell not working

I have an R script that is working perfectly when I execute it from R Studio, but when I am trying to run it using a VBA code that calls Shell, the code runs, the command window shows up and closes, but it does not generate the result file. It does not throw any error. Can someone see what the problem is?

This is the address of the folder that has the Rscript.exe file: C:\Program Files\R\R-3.4.4\bin\x64\

VBA Code:

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)

End Sub

R Script:

library(RODBC)
library(dplyr)
#library(data.table)
library(tidyr)
library(tictoc)
library(tidyverse)
library(lubridate)
library(xlsx)
library(sqldf)

#set working directory
setwd("C:/Ibos/R/WF_Metrics")


my_server="servername"
my_db="dbname"
my_username="username"
my_pwd="password"


db <- odbcDriverConnect(paste0("DRIVER={SQL Server};
                               server=",my_server,";
                               database=",my_db,";
                               uid=",my_username,";
                               pwd=",my_pwd))


sql="select * from dbo.metricsfinal"

df <- sqlQuery(db,sql)


myfile="results"
write.csv(df, file = paste0(myfile,"_test",".csv") ,row.names=FALSE)

Edit:

After Oliver's answer and some helpful comments by other, I found out that the problem is the xlsx package. Now I need to figure out how to resolve this issue. I prefer to use this package rather than look for other packages/options, any help is appreciated. Here is the error:

Error: package or namespace load failed for 'xlsx': .onLoad failed in loadNamespace() for 'rJava', details: call: dirname(this$RuntimeLib) error: a character vector argument expected Execution halted

Upvotes: 1

Views: 1728

Answers (1)

Oliver
Oliver

Reputation: 8602

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.

Upvotes: 1

Related Questions