CODEWITHSUNDEEP
rexcelvbardcomclient
user213070
user213070

Reputation: 1

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

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.

Upvotes: 0

Views: 412

Answers (1)

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2233

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)

Upvotes: 0

Related Questions