9

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?

Iorek
  • 571
  • 1
  • 13
  • 31
  • 1
    What happens if you try making is `.xlsx` https://openpyxl.readthedocs.io/en/default/tutorial.html#saving-to-a-file read the last note here. – Tom Myddeltyn Jul 03 '16 at 20:06
  • Yes just reading this. Cheers – Iorek Jul 03 '16 at 20:10
  • 1
    Possible duplicate of [How to save XLSM file with Macro, using openpyxl](http://stackoverflow.com/questions/17675780/how-to-save-xlsm-file-with-macro-using-openpyxl) – Charlie Clark Jul 03 '16 at 20:22
  • I can corroborate that mucking about with file.extension can solve this. I load an `xlsx` with apparenetly some extras, it wouldn't open if sent in a response as "xlsx". It did open in libre office. When the file.extension was changed to `xlsm` the file could be opened. – semiomant Mar 04 '19 at 10:48

2 Answers2

4

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
Tom Myddeltyn
  • 1,307
  • 1
  • 13
  • 27
  • Yes I just read this, sadly. Are there additional libraries that will allow me to build in macros as well as python? – Iorek Jul 03 '16 at 20:10
  • Sorry, no idea. I typically only use basic excel functionality in my tasks. good luck! – Tom Myddeltyn Jul 03 '16 at 20:12
  • 4
    wb = load_workbook(filename='C:\\Users\\Mark\\Documents\\Test.xlsm', read_only=False, keep_vba=True) This solved it, I can now save in xlsm after opening it like this – Iorek Jul 03 '16 at 20:29
  • nice, you should write it up as an answer under "answer my own question" – Tom Myddeltyn Jul 03 '16 at 20:30
  • even with `wb = load_workbook(filename='C:\\Users\\Mark\\Documents\\Test.xlsm', read_only=False, keep_vba=True) ` I cannot open excel and shows `corrupted`, im only writing in cells which have nothing to do with the macro buttons on the excel,anyone knows how to resolve? – Scope Oct 18 '20 at 20:58
4

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.

Gharbad The Weak
  • 1,541
  • 1
  • 17
  • 39