R help - Apr 2010 - how to use Excel VBA's Shell() to call and execute R file

I have an R file R_test.R saved in C:\R_test folder. The R_test.R looks like
this:

setwd("C:/R_test")

rnorm(100)

write(rnorm(100),"data.txt")

q(save="no", runLast = F)

Next I would like use an Excel VBA/macro Shell() function to call R and
execute R_test.R file. The syntax is *Shell(pathname*[*,windowstyle*]*)*



I wrote an Excel macro RunRcode like this:

Sub RunRcode()

 Dim RetVal

 RetVal = "C:\Program Files\R\R-2.10.1\bin\Rgui.exe &
C:\R_test\R_test.R”

RetVal = Shell(RetVal, vbNormalFocus)

End Sub



When this macro is run, the R is opened but R_test.R file is ignored.



If I change RetVal = "C:\Program Files\R\R-2.10.1\bin\Rgui.exe &
C:\R_test\R_test.R”

To   RetVal = "C:\Program
Files\R\R-2.10.1\bin\Rgui.exe&C:\R_test\R_test.R”
(that is, no space before and after the “&” sign as some suggested),  I got
error msg “run-time error 53 file not found”).



Is there anyone can give me a hand on how to make RunRcode macro work?



Thanks

kz

	[[alternative HTML version deleted]]

Hi KZ,

I don't think that I can answer what I think is the precise question - how
to run the R file from VBA but without using RExcel.

However with RExcel installed, I have found it very straightforward to run R
code from within VBA (thanks Erich Neuwirth - RExcel is great).  The VBA
code is:

RInterface.StartRServer
RInterface.RunRFile "C:\Excel_R_script.txt"
RInterface.StopRServer

That is all that it takes.  "Excel_R_script.txt" is just standard R
code, in
a text file.  Is there a reason you don't want to use RExcel?

Guy
-- 
View this message in context:
http://n4.nabble.com/how-to-use-Excel-VBA-s-Shell-to-call-and-execute-R-file-tp2014944p2015718.html
Sent from the R help mailing list archive at Nabble.com.

I realize this is an old post, but thought it's good to answer for other
folks that hit it during a search.

RExcel is fantastic. However, it requires all the users to install RExcel.
So the VBA approach may still be a good alternative in some simple cases,
like the example question.

I think what you need it a wrapper .bat file. Create a .bat file that does
the R execution that you desire. Then call that .bat file using the shell()
command in VBA. Using a wrapper also makes debuging easier. You can run it
from a cmd prompt outside excel to test it and see error messages for
example.

kuna_matata



--
View this message in context:
http://r.789695.n4.nabble.com/how-to-use-Excel-VBA-s-Shell-to-call-and-execute-R-file-tp2014944p4666415.html
Sent from the R help mailing list archive at Nabble.com.