Getting "<checkErrorInfo> 80020009 Error in .COM(x, name, ...) :" in R while accessing excel file

Question

I have automated the process of accessing VBA Macros, from R, using Openxlsx and RDCOMClient packages. Below is the code:

Function:

OpenExcel_5param <- function(directory,filename, MacroName, param1, param2, 
param3,param4, param5)
{

 # Open a specific workbook in Excel:
   data <- paste(c(directory,'/',filename,'.xlsm'), collapse = '')
   xlApp <- COMCreate("Excel.Application")
   xlWbk <- xlApp$Workbooks()$Open(data)

 # this line of code might be necessary if you want to see your spreadsheet:
   xlApp[['Visible']] <- TRUE 
   xlApp$Run(MacroName,param1,param2, param3, param4, param5)

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

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

Code to access this function:

OpenExcel_5param(Common_folder,VBA_CodeFileName,"ConsumptionValidation.MARM",r_extracts,"1. UniqueUPC_Nielsen",10000,original_files,"MARM")

When I run the code, I get the following error:

80020009
Error in .COM(x, name, ...) :

When I ran the code for the first time, it worked fine. From second time onward, it started giving me this error. Same code is working just fine on a different computer. On a third computer, it works fine if I keep the excel file open, but on my computer not working at all. I have tried different version of R, but did not help.

Codd

Please post what the macro does as it may be launching other processes. – Satisfactory

Did you run your code twice in the same R session or you ran your code a second time once you R session was restarted? – Perigee

Answer 1

If your code only works once per one R session (i. e. does not work twice in the same R session, but will work again if you restart R), you can consider the following approach :

library(callr)

OpenExcel_5param <- function(directory,
                             filename, 
                             MacroName,
                             param1,
                             param2, 
                             param3,
                             param4,
                             param5)
{
  library(RDCOMClient)
  
  # Open a specific workbook in Excel:
  data <- paste(c(directory,'/',filename,'.xlsm'), collapse = '')
  xlApp <- COMCreate("Excel.Application")
  xlWbk <- xlApp$Workbooks()$Open(data)
  
  # this line of code might be necessary if you want to see your spreadsheet:
  xlApp[['Visible']] <- TRUE 
  xlApp$Run(MacroName,param1,param2, param3, param4, param5)
  
  # Close the workbook and quit the app:
  xlWbk$Close(FALSE)
  xlApp$Quit()
  
  # Release resources:
  rm(xlWbk, xlApp)
  gc()
}

callr::r(func = OpenExcel_5param, args = list(directory, filename, MacroName, param1, param2, param3, param4, param5))

Perigee