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

user_13301104 0
Viewed 1053

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!

excel xml python
2 Answers

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')
user_58603750

I have a same issue when opening the excel file. One of the drawing shapes was missing. After I update the openpyxl version to 3.0.10 (original 2.6), the problem is resolved.

user_41664630
Related