How to Save Xlsm File With Macro, Using Openpyxl

is there any way to create a .xlsm file from scratch in python?

Thanks to both Alexander Pushkarev and APhillips for helping out with this question. Going off of Alexander's post I was able to figure out a hack to get this to work. I'm not really proud of this, but it works.

Running Alexander's code I get this error:

Exception ignored in:
Traceback (most recent call last): File
"/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py",
line 1819, in del
self.close() File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/zipfile.py",
line 1836, in close
self.fp.seek(self.start_dir) ValueError: I/O operation on closed file.

I played around with this and found out that if I took out keep_vba=True from the load_workbook function the code ran but I still got the error I noted above when trying to open the .xlsm file with Excel.

So, looking at the latest error I saw the last line says

I/O operation on closed file.

I looked at openpxyl documentation and tried opening the file without the keep_vba=True option before opening it with keep_vba=True and it worked.

So excuse this ugly code, but this will work to create a .xlsm file from scratch without depending on any existing files (copy and paste ready):

from openpyxl import Workbook
from openpyxl import load_workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
wb.save('new_document.xlsm')
wb1 = load_workbook('new_document.xlsm')
wb2 = load_workbook('new_document.xlsm', keep_vba=True)
wb2.save('new_document.xlsm')

Writing to existing xlsm files

No, it means that if there is a file with the same name, it will not warn you, it will just replace the file.

You can modify existing excel files, however, it doesn't save modifications, rather it overwrites the old file with a new file with modifications.

Related Topics