R script RDCOMClient can't open xlsx file

Question

I am trying important data that I've organized in R into an existing table of an existing workbook (xlsx file) I have the following code after the data curation is complete:

file <- "U:/Reporting/Template-Business_Report.xlsx"

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open(Filename = file)
Sys.sleep(1)
sheet <- xlWbk$Worksheets("DataSet")
range <- sheet$Range("Table1")
range[["Value"]] <- asCOMArray(table)
newfile <- paste(sub("Template", state, file),format(Sys.time(), format = "%y%m%d %H%M"))


xlWbk$Save()                  # save the workbook
xlWbk$SaveAS(Filename =asCOMArray(newfile))  # save as a new workbook
xlApp$Quit()

however, no matter what I do for the file name (I'll show examples of what I've tried below) I get the following error.

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

The file exists, it just doesn't work. I wonder if it's open (in the background) and that's why it throws an error, but it won't let me close it either.

I also try:

file <- "U:\\Template-Business_Report.xlsx"
file <- "Template-Business_Report.xlsx"

as well as this for xlWbk.

xlWbk <- xlApp$Workbooks()$Open(Filename = file , 0, TRUE)

Why can't RDCOMClient find my file?

Answer 1

I was also facing the same problem but this error comes due to cache. Try doing 2 things:

1. Remove your xlApp and xlWbk objects from the session using rm("xlApp", "xlWbk")
2. Clear the cache using gc()
3. Restart your R session (You can skip this step if the error is resolved after the above 2 steps).

Cheers!

Keep Coding!