0

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

Whizkid95
  • 271
  • 4
  • 14
  • You can import the file into R, perform all computations, and save the resulting dataframe as an excel file. Is there a reason you don't follow that path? – TTS Oct 08 '20 at 21:13
  • Yes, so I'm building it for someone else who does not have any R or Python knowledge. And I want them to be able to run it (and therefore change the data in the Excel) while being in Excel – Whizkid95 Oct 09 '20 at 05:56

1 Answers1

0

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)
Emmanuel Hamel
  • 1,769
  • 7
  • 19