I am trying to build a script in VBA that will be executed in R. I have already been able to execute R scripts via VBA's shell command, but been unable to do so when I add arguments to the code.
Following is my VBA code to execute 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 path As String
Dim var1
Dim var2
Dim var3
var1 = Sheets("Command").Range("ticker").Value
var2 = Sheets("Command").Range("type").Value
var3 = Sheets("Command").Range("period").Value
path = "Rscript C:\Documents\RProjects\FA.R " & var1 & " " & var2 & " " & var3
errorCode = shell.Run(path, style, waitTillComplete)
Following is the code I am running in R:
setwd("C:/Documents/RProjects/WorkingLibrary")
library(quantmod)
args = commandArgs(trailingOnly=T)
var1 = as.character(args[1])
var2 = as.character(args[2])
var3 = as.character(args[3])
x = getFinancials(var1)
Data = viewFinancials(get(x), type=var2, period=var3)
write.csv(Data,'TestData.csv')
I know this is failing because the csv file is not saving down, therefore there is no data being pulled by R. If I pass no variables and just have excel run an R script that prints say, "Hello World!" and saves the csv, that works. But when I add the variable arguments it fails. If I hard code the variables in the R script, the csv will work, therefore I know the R code is sound, but not picking up on the arguments when passed via command line.
I do not know if the problem is on the VBA end or the R end. All I can identify is that the problem is in either:
1) the passing of the arguments to the command line
2) the accepting of the argument in R
3) a combination of both.
Any ideas? Thanks.
