我该如何从VBA运行一个R脚本?假设我有一个存储在C:\XXX\testR.R的R脚本。
我尝试使用Shell,但并不是非常成功。
Public Sub RunRTest()
Shell ("Rscript test.r")
End Sub
注意文件位置,可能需要更明确的Shell dim语句...例如,请用以下行替换您VB中的代码
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 = """" & Cells.Range("RhomeDir") & """ """ & Cells.Range("MyRscript") & """"
errorCode = shell.Run(path, style, waitTillComplete)
在Excel中,一个带有命名引用RhomeDir
的单元格包含文本:
C:\Program Files\R\R-3.2.3\bin\x64\rscript
而MyRscript
包含文本C:/Documents/Rworkings/Rscripttest.s
。请注意,Unix中使用反斜杠和.s或.r作为后缀,而VB则将""替换为"以在路径表达式中使用双括号(再加上外部括号表示字符串)。同时,文件名中不应该包含空格。
通过搜索VBA shell可以找到上述shell命令的完整dim语法。
我将所有内容放在一个易于调用的函数中。输出是 shell.run 的输出,它是一个整数:
运行 R 脚本的函数:
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.4.4\bin\x64\rscript","C:\Ibos\R\WF_Metrics\Abe.R")
End Sub
或者
Sub Demo()
Dim iEerrorCode As Integer
Dim WS as WorkSheet
Set WS=ThisWorkBook.Worksheets("Sheet1")
iEerrorCode = Run_R_Script(WS.Range("A1"),WS.Range("A2")) 'cell A1=adderess of R application and cell A2 is the address of your R file, one can use a named range too
End Sub