Smart way to save a data table on open workbook with RDCOMClient

Question

I have a workbook with two worksheets say simply 1 and 2. Worksheet 1 has a button that when clicked, runs a VBA sub to call an R script. Using the package RDCOMClient, the R script connects to the (still open) workbook, reads some data tables off sheet 1, computes some stuff and then outputs a data table t. What I want to do is output t to sheet 2.

What I currently do:

for (i in seq(t[,.N])){
    for (j in seq(length(t))){
        ws$Cells(i,j)[["Value"]]<-t[[i,j]]; #ws is a reference to Sheet2
    }
}

Now the problem with this is that it's insanely slow. It takes like 1-2 minutes to write a 60x130 table. Does anyone know how to make this faster?

PS: I want to update an open workbook, not save to a different file. So that's why I didn't try something like openxl etc.

Create a 2D array and write it to the worksheet in a single call - otherwise the cell-by-cell cross-process operations are super-slow. Not familar with R but something like `ws$Cells(i,j).Resize(numRows, numCols)[["Value"]]<-2DArray` — Tim Williams, Mar 15 '19 at 22:08
This appears to have some useful examples: http://www.omegahat.net/RDCOMClient/Docs/introduction.html — Tim Williams, Mar 15 '19 at 22:13
The problem seems to be that ws$Range(ws$Cell(),ws$Cell())[["Value"]] seems to be a doubly nested list and when I try setting it equal to a matrix I get a non-conforming type error. — user213070, Mar 15 '19 at 22:20
I don't have R or any R experience, so i can't offer anything further here... — Tim Williams, Mar 15 '19 at 23:21

score 0 · Answer 1 · answered Oct 04 '22 at 02:33

You can consider using the function asCOMArray of the RDCOMClient package :

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

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)

Smart way to save a data table on open workbook with RDCOMClient

1 Answers1