Python openpyxl saving xlsm file gets error when opening

Question

I have a script that opens an xlsm file and an xlsx file. It modifies the xlsm with data from the xlsx, then saves the xlsm file. When i open that xlsm file after the script is run, I get an error shown in the image.

The file then works fine but I get an XML error shown below:

The code I am using is:

import openpyxl
destwb = openpyxl.load_workbook(filename="C:\\627 Data\\winphy\\071-000-022-00 627 data.xlsm", read_only=False, keep_vba=True)

.....Code.....

destwb.save(filename="C:\\627 Data\\winphy\\071-000-022-00 627 data2.xlsm")

the repaired summary section is not closed, "summary" is missing — Ari Gold, Jan 05 '17 at 13:35
The Excel file errors are unfortunately not very helpful and without the file we can't help you. — Charlie Clark, Jan 05 '17 at 16:54
I am not sure how I can attach the file to the thread but once i open the file and save it again, The errors are gone. — mickNeill, Feb 16 '17 at 15:52
@mickNeill It seems that you saved it as a xls. I am not sure if `save` from the `openpyxl` can save xlsm file this way! — Srđan Popić, Feb 20 '18 at 09:34

score 2 · Answer 1 · answered Mar 15 '20 at 00:38

I ran into something similar and pieced largely recycled code from this solution by Joost in this question: How to save XLSM file with Macro, using openpyxl

Apparently, openpyxl doesn't read or preserve all of the magic macro parts of an xslm when opening and saving. Since the files are in a zip format, the solution:

Saves your work as an xlsx
Opens the original xlsm as a zip and extracts the key parts
Creates a new zip with the data from your saved xlsx and the above key parts
Renames that as a xlsm

I took the sample code, turned it into a usable replacement for workbook.save(), fixed a missing file (likely Excel change since the original solution), added zip compression and creation of a backup file to the mix. May this do what you need.

def saveXlsm(wb, xlsmname):
'''Some crazy workaround to fix what openpyxl cannot when recreating an xlsm file. 
   Use as replacement for workbook.save()
'''
import zipfile
from shutil import copyfile
from shutil import rmtree

# Unzip original and tmp into separate dirs
PAD = os.getcwd()
wb.save('tmp.xlsx')
with zipfile.ZipFile(xlsmname, 'r') as z:
    z.extractall('./xlsm/')
with zipfile.ZipFile('tmp.xlsx', 'r') as z:
    z.extractall('./xlsx/')
# copy pertinent left out macro parts into tmp
copyfile('./xlsm/[Content_Types].xml','./xlsx/[Content_Types].xml')
copyfile('./xlsm/xl/_rels/workbook.xml.rels','./xlsx/xl/_rels/workbook.xml.rels')
copyfile('./xlsm/xl/vbaProject.bin','./xlsx/xl/vbaProject.bin')
copyfile('./xlsm/xl/sharedStrings.xml','./xlsx/xl/sharedStrings.xml')
# create a new tmp zip to rebuild the xlsm
z = zipfile.ZipFile('tmp.zip', 'w', zipfile.ZIP_DEFLATED)
# put all the parts back into the new Frankenstein
os.chdir('./xlsx')
for root, dirs, files in os.walk('./'):
        for file in files:
            z.write(os.path.join(root, file))
z.close()
os.chdir(PAD)
# humanize Frankenstein
bakname = xlsmname + '.bak'
if os.access(bakname, os.W_OK):
    os.remove(bakname)
os.rename(xlsmname, bakname)
os.rename('tmp.zip', xlsmname)
#clean
rmtree('./xlsm/')
rmtree('./xlsx/')
os.remove('./tmp.xlsx')

Python openpyxl saving xlsm file gets error when opening

1 Answers1