从 VBA 运行 R 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11597626/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running R scripts from VBA
提问by Joyce
How can I run a R script from VBA? Say I have a R script stored as C:\XXX\testR.R
如何从 VBA 运行 R 脚本？假设我有一个 R 脚本存储为 C:\XXX\testR.R
I tried using Shell, but not quite successful.
我尝试使用 Shell，但不太成功。
回答by Dieter Menne
Public Sub RunRTest()
Shell ("Rscript test.r")
End Sub
回答by hopethishelps
Note be careful with your file locations and may need more explicit Shell dim statements....e.g. replace with these lines in your VB
请注意您的文件位置，可能需要更明确的 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)
where, in Excel a cell with a named reference RhomeDircontains text
其中，在 Excel 中，具有命名引用的单元格RhomeDir包含文本
C:\Program Files\R\R-3.2.3\bin\x64\rscriptand
C:\Program Files\R\R-3.2.3\bin\x64\rscript和
MyRscriptcontains text C:/Documents/Rworkings/Rscripttest.s
MyRscript包含文本 C:/Documents/Rworkings/Rscripttest.s
noting the unix R backslash and .s or .r postfix and VB replaces "" with " to give double brackets in path expression (plus further outside brackets to denote string). Also not a good idea to have spaces in your file name.
注意 unix R 反斜杠和 .s 或 .r 后缀，VB 将 "" 替换为 " 以在路径表达式中提供双括号（加上更多的外括号来表示字符串）。在文件名中包含空格也不是一个好主意。
The full dim syntax of the shell command above was found by searching for VBA shell.
通过搜索 VBA shell 找到了上述 shell 命令的完整语法。
回答by Ibo
I put everything in a function that can be called easily. The output is the shell.run output, which is an integer:
我把所有东西都放在一个可以轻松调用的函数中。输出是 shell.run 输出，它是一个整数：
Function to Run an R Script:
运行 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
Examples how to call:
调用示例：
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
OR
或者
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
