Sheet-based macros are removed on Workbook save

[nicojx]

Describe the bug
Loading a macro-enabled workbook (via wb_load()) with sheet-based VBA macros (such as Private Sub Worksheet_SelectionChange(ByVal Target As Range)) and saving it again using wb_save() results in the removal of those macros from the sheets.

What seems to happen is that openxlsx2 creates copies of the original sheets without macros (the visible sheet name is the same but the Excel-internal sheet name is different), while the original sheets remain with macro but without the data / invisible. This can then be seen in the VBA view sheet list (see screenshot). Originally only one sheet (Sheet1 was both internal and external name) existed.

To Reproduce

1. Create a Macro-enabled Excel file
2. Add a sheet-based macro (such as Private Sub Worksheet_SelectionChange(ByVal Target As Range)). This doesn't have to 'do' anything. Save.
3. View the sheets in the Excel macro view (Alt+F11)
4. Load the workbook via wb_load()
5. Save the workbook object via wb_save()
6. Re-open the workbook in Excel, and examine the sheets in the macro view (Alt+F11)

Expected behavior
The same sheets exist after save as before save, with macros attached to sheets.
The issue does not appear on non-macro-enabled (xlsx) workbooks.
The openxlsx had the same behaviour.

Screenshots

Example files
I cannot upload macro-enabled Excel files but the problem should be easily reproducable.

[JanMarvin]

Could you please mail me an example? The mail address is in my GitHub profile. Thanks!

[JanMarvin]

Hi @nicojx , thanks for providing the example file (and not highjacking me with strange Macros 😅). A potential fix in #417 could you please test this?

remotes::install_github("JanMarvin/openxlsx2", ref = "gh_issue_416")

[nicojx]

Great, that fixes it indeed! Thanks so much!