2

I'm using the basic functions of openxlsx package for writing data on excel file. When I use .xlsx files for writing data, there is no problem. When I try to use .xlsm (files with macro) for writing data, after saving the .xlsm file, there is a duplication of sheets in the Vba Project and these new sheets are empty.

My code is as follows:

   OutputBook = loadWorkbook("DataImportOutput.xlsm")

   writeData(OutputBook, "Database", MyData, rowNames = TRUE, startCol=1, startRow=1)

   saveWorkbook(OutputBook, "DataImportOutput.xlsm", overwrite = TRUE)

At the end of the R code, "DataImportOutput.xlsm" has a duplication of sheets in is Vba Project. Using the same file, but without macros (.xlsx), there is no duplication.

Why does this duplication of worksheets happen? There is a method to avoid this duplication of sheets with .xlsm files?

Community
  • 1
  • 1
Mav21
  • 21
  • 3
  • You need to clarify *exactly* what you mean with "multiplication of worksheets/objects in the VBA project". How many worksheets/objects are in the VBA project of DataImportOutput.xlsm before the code runs, and after? The workbook is macro-enabled (.xlsm), does it open in Excel, and are there macros that run when it opens (e.g. a `Workbook_Open` handler in `ThisWorkbook`)? – Mathieu Guindon Mar 28 '17 at 15:43
  • Before the code runs in R, the DataImportOutput.xlsm has 8 worksheets. After the R's code execution, the DataImportOutput.xlsm has 16 worksheets but these new 8 worksheets are visible only in the VBA project (if I open the xlsm file in Excel, I have the initial 8 sheets with the modifications made with R code). These new eight sheets are empty. The VBA macro are only for changing format in a later step. – Mav21 Mar 28 '17 at 16:01
  • Looks like your R code is corrupting the VBA project somehow. I suppose `writeData` isn't your code? Make sure all COM objects are properly released. – Mathieu Guindon Mar 28 '17 at 16:07
  • Run `names(OutputBook)` between the commands to see if the problem is with loading the workbook or when it is modified – manotheshark Mar 28 '17 at 16:16

0 Answers0