search

ycphs / openxlsx

openxlsx - a fast way to read and write complex xslx files
https://ycphs.github.io/openxlsx/
Other
220 stars 74 forks source link

Sheet-based macros are removed on Workbook save #393

Closed nicojx closed 9 months ago

nicojx commented 1 year ago

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

What seems to happen is that openxlsx 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 loadWorkbook()
  5. Save the workbook object via saveWorkbook()
  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. I could not test it on macro-enabled (xlsm) workbooks without macros, as Excel refused to open these after saveWorkbook() saved them.

Screenshots Screenshot 2022-11-03 230835

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

nicojx commented 1 year ago

Realised this is similar to / the same issue as: https://github.com/ycphs/openxlsx/issues/44.

I have very little programming experience, but happy to try to help resolve it.

JanMarvin commented 1 year ago

@nicojx , you could see if this works with openxlsx2, it's unlikely to be fixed here

nicojx commented 1 year ago

Exactly the same behaviour with openxlsx2 -- I'll raise it there, too. Thanks!

github-actions[bot] commented 9 months ago

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

github-actions[bot] commented 9 months ago

This issue was closed because it has been stalled for 7 days with no activity.