Execute an R script from Excel

[FelipeCarrillo]

I am trying to execute an R script using Rscript in VBA Excel book. However, excel.link opens a new instance of excel when the R script is executed and ignores my workbook. How can I point out to my workbook? xl.workbook.activate("my_book") doesn't seem to work. Here is what my R script looks like;

library(excel.link)
 setwd("C:/Yuba_Project")
getwd()
FinalData <- head(iris)
 FinalData
 #xl.workbook.open("Excel_link.xlsm")
 xl.workbook.activate("Excel_link.xlsm")
#xl.sheet.activate("summary")
xlc[a2:h2] = NA
  xlc[a1] = FinalData

And I am trying to execute the above R script from Excel VBA like below:

Sub test()
Dim myPath As String
'ChDir ThisWorkbook.path
myPath = "RScript " & "C:\Users\my_file\Desktop\R_code_5-20-22\Rtest.r"
Call shell(myPath, vbHide)

Question: Does the book has to be close in order to execute the R script?

[gdemin]

It seems a new instance of Excel is activated. To call R from Excel it is better to use functions from the workbook in the package. You can find it in the 'library\excel.link\xlsx\R_connection_examples.xlsm' or here: https://github.com/gdemin/excel.link/blob/master/inst/xlsx/R_connection_examples.xlsm Particulary, you can be interested in ExecInR VBA function.

[FelipeCarrillo]

Thanks for the clarification. A follow up question if you dont mind. Can I use ExecInR this way: ExecInR("plot(rnorm(100))") Felipe D. CarrilloFish and Wildlife BiologistDepartment of the InteriorU.S. Fish and Wildlife, San FranciscoBay Delta OfficeCalifornia, USAhttps://www.fws.gov/office/san-francisco-bay-delta-fish-and-wildlife

On Wednesday, September 11, 2024 at 03:23:09 AM PDT, Gregory Demin ***@***.***> wrote:  

It seems a new instance of Excel is activated. To call R from Excel it is better to use functions from the workbook in the package. You can find it in the 'library\excel.link\xlsx\R_connection_examples.xlsm' or here: https://github.com/gdemin/excel.link/blob/master/inst/xlsx/R_connection_examples.xlsm Particulary, you can be interested in ExecInR VBA function.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

[gdemin]

You need to define graphic device. For example, ExecInR ("png('c:/temp/1.png');plot(rnorm(100));dev.off()") will save plot to c:/temp/1.png

[FelipeCarrillo]

This is great!!! I will try to practice to get more familiar with the functions. Where can I read more about the functions you use in the workbook? I want to learn more about  ExecInR, ExecRangeInR etc.  Also, I want to let you know just in case you are not aware of it. I noticed that the 'Environ' vba function in the workbook does not work. It returns empty when the code is looking for 'R_HOME'.  In the R console, if I type: Sys.getenv("R_HOME"), I get the R_HOME directory, However, with Environ in vba returns empty. Thank you for the workbook. Felipe D. CarrilloFish and Wildlife BiologistDepartment of the InteriorU.S. Fish and Wildlife, San FranciscoBay Delta OfficeCalifornia, USAhttps://www.fws.gov/office/san-francisco-bay-delta-fish-and-wildlife

On Thursday, September 12, 2024 at 07:05:29 AM PDT, Gregory Demin ***@***.***> wrote:  

You need to define graphic device. For example, ExecInR ("png('c:/temp/1.png');plot(rnorm(100));dev.off()") will save plot to c:/temp/1.png

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>