CODEWITHSUNDEEP
rexcelvbacommand-linequantmod
HonnSolo
HonnSolo

Reputation: 165

VBA to R through Command Prompt Shell

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.

Upvotes: 2

Views: 665

Answers (1)

HonnSolo
HonnSolo

Reputation: 165

I am an idiot. I had my path wrong. This code works perfectly and passes the variables like a dream. If anyone would like to reference or use this code for a similar project, you are free to do so. Just ensure you have the right file path :P

Upvotes: 3

Related Questions