将变量从VBA传递到R脚本 [英] Passing Variables from VBA to R-Script

问题描述

我目前正在研究连接R和Excel的接口.我到目前为止可以通过VBA宏执行R程序. 我使用了在 http://shashiasrblog.blogspot.fr/2013/10 /vba-front-end-for-r.html

I am currently working on an Interface which connects R and Excel. I am up to now able to execute a R programm via a VBA Macro. I used the recommandations given on http://shashiasrblog.blogspot.fr/2013/10/vba-front-end-for-r.html

由于我的R脚本调用了外部txt文件，因此在更改相应文件的目录时最好不要手动更改R代码. 因此，问题是，是否可以将VBA脚本中的变量分配给R .

Since my R-Script invokes external txt-files, it would be nice not to change the R code manually, when changing the directories of the corresponding files. So the question is, is it possible to assign variables from a VBA Script to R.

在所示网站的帮助下，我未能成功.有人遇到这个问题并且知道如何解决吗?您会发现附有我尝试从网站改编的代码示例.

I did not manage to be successful with the help of the indicated website. Did anybody meet this problem and knows how to resolve it? You will find attached a code example which I tried to adapt from the website.

问候，

大卫

 Sub RunRscript()
'runs an external R code through Shell
'The location of the RScript is 'C:\R_code'
'The script name is 'hello.R'
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 var1, var2 As Double
var1 = Tabelle1.Range("D5").Value
var2 = Tabelle1.Range("F5").Value

Dim path As String
path = "RScript C:\Users\David\Desktop\Test\test.R" & var1 & " " & var2

errorCode = shell.Run(path, style, waitTillComplete)

End Sub



args<-commandArgs(trailingOnly=T)
# cat(paste(args,collapse="\n"))
sink('C:/Users/David/Desktop/Test/test.R',append=F,type="output")

var1<-as.numeric(args[1])
var2<-as.numeric(args[2])

var1<-5
var2<-2
a<-var1+var2
write.table(a, file = "C:/Users/David/Desktop/bla.txt", sep = ",", col.names = NA,
            qmethod = "double")
sink(NULL)

推荐答案

快到了.首先，您不需要sink()行，因为您无需写任何文本文件，当然也不必写用于传递参数的R脚本.如果您在链接中看到，则说明正在创建文本文件并使用sink()和cat()更新.只需删除R对中的对即可，因为您不需要这样做.我确定您知道代码的底部应该在引用的test.R脚本中，而不是在VBA中.

Almost there. First, you don't need the sink() lines as you are not writing to any text file and certainly not to the very R script used for passing the arguments. If you see in your link, a text file is being created and updated with sink() and cat(). Simply, remove the pair in your R code as you do not do the same. I'm sure you are aware the bottom portion of your code should be in the referenced test.R script and not in VBA.

第二，您需要在文件名称后的路径字符串中添加一个空格:

Second you need to add a space to your path string after the file designation:

path = "RScript C:\Users\David\Desktop\Test\test.R " & var1 & " " & var2

或

path = "RScript C:\Users\David\Desktop\Test\test.R" & " " & var1 & " " & var2

最后，您将覆盖参数值var1和var2.删除第二个变量声明，以查看带有Excel数据的文本文件输出.

Finally, you overwrite the argument values var1 and var2. Remove the second declaration of variables to see text file output with your Excel data.

这篇关于将变量从VBA传递到R脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！