如何将 Excel/VBA 中的参数传递给 Rstudio 中的脚本 [英] How to pass an argument from Excel/VBA to a script in Rstudio
问题描述
我正在尝试使用 VBA 中的 Rstudio 打开 R 脚本，同时将参数传递给 R 脚本，然后我可以使用 commandArgs() 访问该参数.
I am trying to open a R script with Rstudio from VBA, while at the same time passing an argument to the R script, that I can then access with commandArgs().
问题与此处描述的问题非常相似:
The problem is very similar to the one described here:
WScript.Shell 运行在路径中带有空格和来自 VBA 的参数的脚本
然而，这个解决方案虽然很好，但似乎对我不起作用.
However, the solution, albeit very good, does not seem to work for me.
这是我使用的 VBA 代码:
Here is the VBA code I am using:
Sub RunRscript()
ActiveWorkbook.Save
Dim shell As Object
waitTillComplete As Boolean, _
style As Integer, _
errorcode As Integer, _
path As String, _
var1 As String
Set shell = VBA.CreateObject("WScript.Shell")
waitTillComplete = True
style = 1
var1 = Range("F3").Value
path = Chr(34) & "C:\Program Files\RStudio\bin\rstudio.exe" & Chr(34) & " " &
Chr(34) & "C:\Users\LI\Downloads\starting_code_v3.R"
'& Chr(34) & " " & Chr(34) & "var1" & Chr(34)
errorcode = shell.Run(path, style, waitTillComplete)
End Sub
如您所见，我使用的代码与上面提供的链接中的代码几乎相同，但添加了一些内容.也就是说，我将 var1 定义为单元格 F3 的内容(在我的例子中，单元格包含文件的路径，但我想它可以是任何东西).
As you can see, I am using almost the same code as in the link provided above, with a few additions. Namely, I define var1 as the contents of the cell F3 (in my case, the cell contains the path of a file, but I suppose it could be anything).
现在，如果我按照上面介绍的方式运行代码，它就会运行，并启动 RStudio 并打开脚本.但是，如果我将下面 1 行注释掉的代码添加到变量中(即，如果我尝试在传递参数 var1 的同时启动脚本)，RStudio 将打开，但脚本没有，也没有传递任何值
Now, if I run the code as presented above, it works and it launches RStudio and opens the script. However, if I add the code commented out 1 line below to the variable (i.e. if I try to launch the script while at the same time passing the argument var1), RStudio opens, but the script doesn't, nor is any value passed on.
任何建议都会有所帮助.
Any suggestion would be helpful.
请注意，我已经在 stackoverflow 和 google 上查看了所有可能的类似主题，并尝试了大量的引号和双引号组合.(我不是很精通VBA).
Please note that I have looked in every possible similar topic on stackoverflow and on google, as well as trying loads of combinations of quotes and double quotes. ( I am not very proficient in VBA).
另请注意:
- 运行 Win7
- 运行 RStudio 1.1.383 &R 3.4.2
- 我不想使用 cmd 或其他工具.这需要通过一个链接到 vba 代码的按钮从 excel 工作簿运行 - 因为它将被完全的新手使用.
- 我目前不想使用 RScript；也许我会在完成 RStudio 中的所有代码后考虑.但是现在，我需要能够通过 commandArgs 读取 RStudio 中的参数并进行一些操作.
如果可能，请在考虑上述情况的情况下提供您的建议.
Please if possible offer your advice taking into account the above.
我希望以上所有内容都有意义，否则请要求澄清.
I hope all of the above makes sense, otherwise please ask for clarification.
提前致谢.
推荐答案
问题在于 RStudio 而不是您的代码.RStudio 不接受命令行参数:
The problem is RStudio and not your code. RStudio doesn't accept command line arguments:
旧答案:
我看到的问题是您将文本var1"而不是名为 var1 的变量的内容放入路径中.我用堆叠引号替换了你的 Chr(34) ，因为我更容易跟踪.如果这对您来说可读性较差，我深表歉意.我测试了字符串，它确实将 Var1 的内容作为命令行参数提供.
The problem I am seeing is that you are putting the text "var1" in to the path instead of the contents of the variable called var1. I replaced your Chr(34) with stacked quotes because it's easier for me to keep track of. I apologize if this looks less readable to you. I tested the string and it does feed the contents of Var1 as a command line argument.
试试这个:
Option Explicit
Public Sub RunRscript()
ActiveWorkbook.Save
Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Long: style = 1
Dim errorcode As Long
Dim path As String
Dim var1 As String
var1 = ActiveSheet.Range("F3").Value
path = """C:\Program Files\RStudio\bin\rstudio.exe"" ""C:\Users\LI\Downloads\starting_code_v3.R"" """ & var1 & """"
errorcode = shell.Run(path, style, waitTillComplete)
End Sub
这篇关于如何将 Excel/VBA 中的参数传递给 Rstudio 中的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
