I'm developing an economic model in Excel and R. It is composed by two parts: an Excel/VBA shell, as a framework for the front-end user interface; and an Rscrit file used as a back end with R and performing all the calculations by means of the deSolve package. I'm passing the necessary arguments from Excel/VBA by means of the following code I have found in http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html
For the Excel/VBA:
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 = Sheet1.Range("D5").Value
var2 = Sheet1.Range("F5").Value
Dim path As String
path = "RScript C:\R_code\hello.R " & var1 & " " & var2
errorCode = shell.Run(path, style, waitTillComplete)
End Sub
And for the R code:
# Accepts two numbers and adds them
# Re-directs the console output to a file 'hello.txt'
# The file is created in the directory 'C:\R_code'
args<-commandArgs(trailingOnly=T)
# cat(paste(args,collapse="\n"))
sink('C:/R_code/hello.txt',append=F,type="output")
cat('Hello World')
var1<-as.numeric(args[1])
var2<-as.numeric(args[2])
cat('\nThe result of adding',var1,'to',var2,'is',var1+var2)
sink(NULL)
It works fine, but the only drawback is that it takes almost an hour to do the calculations in the background and the only thing the user is able to see is the black screen from the command prompt without any other information on the steps the model is running and the progress of the model calculations.
What I'm intending to do is something similar to what this VBA code is doing:
Sub ShellAndWait(pathFile As String)
With CreateObject("WScript.Shell")
.Run pathFile, 1, True
End With
End Sub
'Example Usage:
Sub demo_Wait()
ShellAndWait ("R.exe")
Beep 'this won't run until Notepad window is closed
MsgBox "Done!"
End Sub
This is to say. Open the R terminal in the command prompt.
Now the only missing action is to run the Rcode in the R terminal opened in the command prompt. Any ideas on how to do it, so the user would be able to see the progress during the execution of the model? Kind regards,
Daniel