Passing Variables from VBA to R-Script

Question

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

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.

Greetings,

David

 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)

Answer 1

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

OR

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

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