时间戳

首页 > 解决方案 > 使用Shell从VBA内部执行R脚本不起作用

r - 使用Shell从VBA内部执行R脚本不起作用

问题描述

我有一个 R 脚本，当我从 R Studio 执行它时它运行良好，但是当我尝试使用调用 Shell 的 VBA 代码运行它时，代码运行，命令窗口显示并关闭，但它不生成结果文件。它不会抛出任何错误。有人能看出问题所在吗？

这是包含该文件的文件夹的地址Rscript.exe：C:\Program Files\R\R-3.4.4\bin\x64\

VBA代码：

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脚本：

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)

编辑：

在奥利弗的回答和其他人的一些有用的评论之后，我发现问题出在xlsx包上。现在我需要弄清楚如何解决这个问题。我更喜欢使用这个包而不是寻找其他包/选项，感谢任何帮助。这是错误：

错误：'xlsx' 的包或命名空间加载失败：'rJava' 的 loadNamespace() 中的 .onLoad 失败，详细信息：调用：dirname(this$RuntimeLib) 错误：预期字符向量参数 执行暂停

标签： rvbashell

解决方案

从 VBA 运行 R 是一项烦人的工作。如果您希望 R 代码直接从 VBA 运行，我建议您研究BERT，它是 Excel 的一个开源插件，让您可以直接从 excel 运行和编写您的 R 代码，从而使您的代码调试更加简单。

也就是说，如果你被 shell.run 卡住了，你可以做一些事情来定位你的问题。

手动调试

在 VBA 中设置断点或打印到控制台的路径。

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

打开命令提示符（按 windows 按钮并输入 cmd，按 enter start, cmd, enter。）

将代码行粘贴到命令提示符中。任何错误都将打印到命令行中，您可以从中找到并更正脚本中的错误。

减少手动调试

现在手动调试可能很乏味，正如我在下面指出的那样，它是特定于系统的。有几个选项可以稍微自动化该过程。这些包括：

  1. 将错误代码直接读入 VBA，将任何输出通过管道传输到 VBA。
  2. 使用像BERT这样的开源集成让您可以直接在 Excel 中编写和调试代码。
  3. 使用系统错误消息来识别错误

第一个选项更复杂，但也是一个非常通用且不错的选项。我在这里建议第一个答案，它提供了一个可以实现此方法的 VBA 模块的链接。但是请注意，它是一个 32 位模块，它需要一些ptrsafe标记，以便 windows api 在 64 位安装的 excel 上工作。通过一些更改，它甚至可以用于直接从 R 读取文本输出（data.frame 等），而对 Excel 的干扰最小。

对于第二个选项，我建议查看BERT网页，该网页为使用实现提供了很好的指南。这样做的缺点是，除了安装 R 之外，任何计算机都需要安装BERT才能让您的 excel 脚本正常工作。

第三种选择是受Chip Pearson 网站启发的。当您的脚本崩溃时，它会向命令行发送一个错误代码，这可以由 Windows 错误代码管理器进行解释。这样做的好处是它很简单，如果您的脚本“不存在”或类似的非 R 特定的常见错误，您将很快意识到这一点。

使用这种方法可以将 R 执行脚本更改为类似

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

在GetSystemErrorMessageText(errorCode)下面的单独模块中调用函数的位置在哪里。

#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

归功于Chip Pearson，尽管它可能不打算用于此用途。执行shell.run(path)where path 是执行 R 脚本的命令，如果失败，现在将返回错误消息。这不会完全消除手动调试，但错误消息将帮助您识别 R 之外的错误，并且通常会提供有价值的错误描述，帮助您在手动调试期间更快地识别错误。

因此，这种方法应该在必要的程度上进行手动调试。

笔记：

  1. 从命令行运行 R 脚本是专门的系统编码。使用“使用编码保存”和使用“System.locale”保存 R 脚本消除了从命令行运行时的大部分问题。但是，它可能会在 UTF8 字符上失败，这完全是另一回事。
  2. 第三种选择是最简单的，因此也是最不通用的。来自 Windows 系统的错误代码只是让您一目了然，并没有指定哪条线路失败，仅Invalid function调用了该线路或类似线路。不能保证这个错误是完全正确的。但是，它确实会为您提供最常见的错误，例如路径错误、函数根本不起作用、调用的变量不存在等。

我希望这将提供一些清晰度并帮助您找到错误。我建议在谷歌上搜索，shell.run用于各种集成的主题是一个已经调查过的主题，如果存在更好的选择，通常建议避免（由于限制）。然而，这通常是一个很好的起点。许多像这样的网站，通过将输出保存到 R 中的文本文件并从 VBA 中读取，展示了如何在 VBA 中使用 R 的输出。确实存在更好的选择，但这可能是最简单的方法。

提问者更新： 经过大量调查，我意识到以下几点：

1-xlsx包使用rJava包，需要先安装Java

2- Java 的 32 位或 64 位版本会影响rJava包和后续xlsx包是否可以成功加载。如果您有 64 位 Windows 操作系统，我建议您安装所有内容（Excel、R、Java）的 64 位版本。

3-默认情况下，R 似乎同时安装了 32 位和 64 位版本，因此您必须指定要使用的版本。在 RStudio 上选择此处的版本：

Tools > Global Options > R Version

默认情况下，我的是 32 位的，即使在我安装了新版本的 Java 后，库（rJava）也在 RStudio 上引发了错误。我将其更改为 64 位，并且可以正常工作。

您也可以使用 R 脚本来执行此操作，但是如果您从 RStudion 内部运行此代码，它将无法正常工作，因为您需要手动更改 R 版本并关闭 RStudion 并再次启动它才能生效

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- 我注意到我的机器上同时安装了 32 位和 64 位 Java。一旦我对环境变量执行了 JAVA_HOME，它就导致了jvm.dll missing error，所以我删除了它，一切又恢复正常

5- 我在一些帖子中看到，如果您使用另一种包装替代方案，xlsx则无需经历所有麻烦来确保一切正常工作。

推荐阅读