通过 RDCOMClient 从 R 运行 Excel 宏，错误 -2147418111

Question

目标是使用 OpenXLSX 将数据添加到现有 Excel 文件，然后使用 RDCOMClient 在同一 Excel 文件中运行宏，并从 R 脚本中保存它。

Excel 宏对数据透视表过滤器和折叠点进行更改，这些更改必须在加载数据后发生。

这个小问题的重现没有问题:

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()

但是，在生产中运行时，我在第一个宏行处收到错误:

xlApp$Run("clear_pt_filters")

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

我怀疑这是由于在 R 继续运行时加载 1-2 MB 文件的时间，而没有收到 RDCOMClient 已准备好执行宏运行请求的信号。

我通过简单地从同一宏行开始再次运行脚本来手动解决此问题。最后，错误只是阻止了完全自动化，电子表格的结果完全符合预期。

编辑:如果我逐行单步执行“生产”版本，则不会出现错误。

我的问题是 1) 错误的原因是什么，2) 我该如何解决自动化中的问题？

谢谢。

Answer 1

我发现最简单的解决方案是使用 Sys.sleep() 插入一秒暂停。

Sys.sleep(1)

所以，从上面看，它看起来像这样，经过编辑:

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()

归功于Is there a "pause" function in R?