Problem saving Macro Enabled Excel Workbook (.xlsm) using Openpyxl. Excel throws an error and starts Repairing the workbook

Question

I need to update a macro enabled excel workbook on a regular basis. Openpyxl is able to read, update and save file successfully without an error.

However, when trying to open the newly saved file in Excel, it throws error 'We found a problem with some content in XX.xlsm. Do you want us to try to recover as much as we can? .... Click Yes.'

After clicking on Yes, it starts repairing the file and removes some key parts mainly /xl/drawings folder;

Removed Part: /xl/drawings/vmlDrawing10.vml part. (Drawing shape) Removed Feature: Named range from /xl/workbook.xml part (Workbook) Removed Records: Comments from /xl/comments/comment5.xml part (Comments) Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing2.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing3.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing4.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing5.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing6.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing7.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing8.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing9.xml part (Drawing shape) Repaired Records: Drawing from /xl/drawings/drawing10.xml part (Drawing shape) Repaired Records: Drawing from /xl/Repaired Records: DRepaired Records: DRepaired Records: DRepaired Records: DRepaired Records: DRepaired Records: DRepaired Records: D

As a result except for the data that the python script updated, the rest of the data gets corrupted and I get #NAME? and #VALUE! error everywhere in the rest of the workbook.

Here's the snippet of the code:

path = 'XXX.xlsm'

from openpyxl import load_workbook
wb = load_workbook(path,keep_vba=True)

ws = wb['Input']

XXX
XXX

ws['I6'].value = date.today()


wb.save('XXX.xlsm')            
wb.close()

I am not sure how to go about it. Please help me fix this error or suggest an alternative as I really need this to work. Thank you!

Answer 1

You can do a workaround by first saving and then loading again before finally saving.

from openpyxl import Workbook, load_workbook
wb = Workbook()
wb.save('your_macro_wb.xlsm')
wb = load_workbook('your_macro_wb.xlsm', keep_vba=True)
wb.save('your_macro_wb.xlsm')