saveWorkbook function saves without macros

Question

I am trying to use a VBA macro to call an R script to clean data based on variables in the excel workbook. I am having troubles pasting the cleaned data back into the excel workbook. I can use saveWorkbook to save it as an .xlsm file, however once I open the file, all my pre-set macros are gone, just disappeared. Is there a way to save the xlsm from R that keeps the macros intact?

As another note, is there a way to write this and have it update so that the file does not need to be closed and reopened? Is there a way to have it run in the same manner as a macro?

wb <- loadWorkbook(file.xlsm) 
writeData(wb, sheet = "DataSet", data, startCol = 1, startRow = 2, colNames = FALSE) 
saveWorkbook(wb, file = file.xlsm, overwrite = TRUE)

It runs fine, but all the macros are gone. The file size goes from 806KB to 27KB

Specify a macro-enabled Excel workbook file format? I don't know what the R equivalent would be, but in the Excel type library what you want is [Workbook.SaveAs](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas) with the `FileFormat` argument set to `xlOpenXMLWorkbookMacroEnabled` (int value is 52) — Mathieu Guindon, Aug 29 '19 at 20:13
when I save the workbook using the function saveWorkbook, I save it as a .xlsm file, so macro enabled. There's only 3 inputs for this function as per the site: https://www.rdocumentation.org/packages/openxlsx/versions/4.1.0.1/topics/saveWorkbook and I am using them all already — morg, Aug 29 '19 at 20:34
No, it saves as a .xlsm file, *so as you specified the file extension should be* - that doesn't mean the actual content serialized in the correct format. It's not impossible that this API does not provide a way to save a macro-enabled workbook. — Mathieu Guindon, Aug 29 '19 at 20:36

DecimalTurn · Accepted Answer · 2019-08-30T16:03:02.030

An alternative solution would be to run your R script with VBA from another Excel file than the one your R script writes to.

You could then use a shell command that can wait for the script to finish before resuming to the rest of your VBA code (See this question for an example).

When the R script returns, your code could then do the remaining manipulations to the data exported by your R script. If you absolutely need the VBA code to be in that newly generated file, you could also add the VBA code modules in the file (see this question for an example) and save it as an xlsm file.

saveWorkbook function saves without macros

1 Answers1