Running Excel macros from R through RDCOMClient, e

Question

The objective is to add data to an existing excel file with OpenXLSX, then run macros in the same Excel file using RDCOMClient, saving it along the way, from an R script.

The Excel macros make changes to the pivot table filters and collapse points that must occur after the data is loaded.

This small, reproduction of the problem works without issue:

library(openxlsx)
library(RDCOMClient)

ds <- cars
tmpl <- './templates/templatetest.xlsm'
datatab <- 'data'
datarng <- 'pdata'
fn <- paste0("./WAR/", "test1.xlsm")

wb <- loadWorkbook(tmpl)
writeData(wb, datatab, ds)
saveWorkbook(wb, fn, overwrite = TRUE)
rm(wb)

# note this doesn't reveal the full actual UNC path
fn <- paste0("./WAR/",
             "test1.xlsm")


xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(fn)

# run macros
xlApp$Run("clear_pt_filters")
xlApp$Run("CollapsePivotFields")
xlApp$Run("toggle_alcItems")

# Close the workbook and quit the app:
xlWbk$Close(TRUE)
xlApp$Quit()

# Release resources:
rm(xlWks, xlWbk, xlApp)
gc()

However, when running in production, I get an error at the first macro line:

xlApp$Run("clear_pt_filters")

Error in .COM(x, name, ...) : Cannot locate 0 name(s) Run in COM object (status = -2147418111)

I am suspicious that it is due to the time of loading the 1-2 MB file while R is proceeding without a signal that RDCOMClient is ready for the macro run request.

I manually resolve this by simply running the script again, starting from the same macro line. In the end, the error just prevents full automation, the spreadsheets turn out exactly as expected.

Edit: If I step through the 'production' version line by line, there is no error.

The questions that I have are 1) what is the the cause of the error, and 2) what can I do to resolve the issue in automation?

Thanks.

Answer 1

I would imagine the issue is that the saveWorkbook() function is meant to save in xlsx format and probably not retaining the macro information in the workbook. What may work better, is to process this entirely with RDCOMClient. Possibly by a method such as this.

library(openxlsx)
library(RDCOMClient)

ds <- cars
tmpl <- './templates/templatetest.xlsm'
newfile <- './templates/templatetestNEW.xlsm'

# Create Excel instance
xlApp <- COMCreate("Excel.Application")

# Open workbook template
xlWB <- xlApp$Workbooks()$Open(tmpl)

# Select the "data" Sheet
xlSheet <- xlWB$Sheets("data")

# Create a dataframe from headers
headers <- t(as.data.frame(colnames(ds)))
# Set range for headers
rng <- xlSheet$Range(xlSheet$Cells(1, 1),xlSheet$Cells(1, ncol(headers)))
# Insert headers
rng[["Value"]] <- asCOMArray(headers)

# Set range for data values
rng <- xlSheet$Range(xlSheet$Cells(2, 1),xlSheet$Cells(nrow(ds)+1, ncol(ds)))

# Add data to Excel sheet
rng[["Value"]] <- asCOMArray(ds)

# Save Workbook
xlWB$SaveAs(gsub("/","\\\\",newfile))

# run macros
xlApp$Run("clear_pt_filters")
xlApp$Run("CollapsePivotFields")
xlApp$Run("toggle_alcItems")

# Close the workbook and quit the app:
xlWbk$Close(TRUE)
xlApp$Quit()

# Release resources:
rm(xlWks, xlWbk, xlApp)
gc()

Answer 2

The simplest solution found that I'm using is to insert a single second pause using Sys.sleep().

Sys.sleep(1)

So, it looks like this, from above, edited:

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(fn)

# run macros
Sys.sleep(1)
xlApp$Run("clear_pt_filters")
xlApp$Run("CollapsePivotFields")
xlApp$Run("toggle_alcItems")

# Close the workbook and quit the app:
xlWbk$Close(TRUE)
xlApp$Quit()

# Release resources:
rm(xlWbk, xlApp)
gc()

Credit to Is there a "pause" function in R?