How to fill an excel file using R, while in the excel file

Question

I'm trying to access an R script via an Excel file. That is, while I'm still in the Excel file, I'd like to run an R script, that fills that same Excel file. The reason for this is that Excel is not computationally efficient enough.

Now I know that normally, you're not able to fill an Excel file via R script when the Excel file is opened. My question is, is there a way to work around this? For instance, by letting the R script close, fill and then open the Excel file I'm calling the script from?

Any help would be much appreciated

Answer 1

Yes, you can! First, you need to open the Excel file with the R package RDCOMClient. It is important that you set the option "Visible" to TRUE. You can manually work on the Excel file and then add data from R into the Excel file. You can also read the data of the Excel file with R.

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlApp[["Visible"]] <- TRUE
xlWbk <- xlApp$Workbooks()$Open("D:\\test.xlsx")

######################################################
#### Some manual work here, add some data, etc.   ####
#### Once some manual work has been done, we can  ####
#### add data with R.                             ####
#### Below, we add data in the range A1:B10       ####
######################################################

x <- rnorm(10)
y <- rnorm(10)
df <- data.frame(x, y)

Sheets <- xlWbk$Sheets() 
nb_Row <- dim(df)[1]
nb_Col <- dim(df)[2]
range_To_Write <- "A1:B10"
obj_Range <- Sheets[[1]]$Range(range_To_Write)
obj_Range[["Value"]] <- asCOMArray(df)