Python to excel, openpyxl and file format not valid

Question

The following is a simple snippet to open a .xlsm file, write a few values to it with python, and save it.

import openpyxl
from openpyxl import load_workbook

def toExcel():
    wb = load_workbook(filename="C:\\Users\\Mark\\Documents\\Test.xlsm")
    ws = wb.worksheets[0]
    ws.cell(row=1, column=1).value = 'foo'
    ws['A2'] = 'bar'
    wb.save("C:\\Users\\Mark\\Documents\\Test1.xlsm")

toExcel()

While the file opens and saves, it mentions file format not valid / corrupt and cannot open. If the .xlsm is removed from the wb.save, it will save and open after selecting excel with Open With. Why is the file format not valid as is?

Answer 1

From here: https://openpyxl.readthedocs.io/en/default/tutorial.html#saving-to-a-file

Note

The following will fail:

>>> wb = load_workbook('document.xlsx')
>>> # Need to save with the extension *.xlsx
>>> wb.save('new_document.xlsm')
>>> # MS Excel can't open the document
>>>
>>> # or
>>>
>>> # Need specify attribute keep_vba=True
>>> wb = load_workbook('document.xlsm')
>>> wb.save('new_document.xlsm')
>>> # MS Excel can't open the document
>>>
>>> # or
>>>
>>> wb = load_workbook('document.xltm', keep_vba=True)
>>> # If us need template document, then we need specify extension as *.xltm.
>>> # If us need document, then we need specify attribute as_template=False.
>>> wb.save('new_document.xlsm', as_template=True)
>>> # MS Excel can't open the document

Answer 2

I found this post because I was trying to create a .xlsm file from scratch using openpyxl. I figured out that I was getting this error because when you load the workbook you need to have keep_vba=True as the second parameter passed to the load_workbook function.

So this is what your load_workbook function should look like:

wb = load_workbook(filename="C:\\Users\\Mark\\Documents\\Test.xlsm", keep_vba=True)

As a side note, here is my post that talks about creating a .xlsm file from scratch using openpyxl.