I am using python 3.8.1 on a mac and am trying to create a .xlsm file from scratch. I have looked at openpyxl and xlsxwriter, both of them are able to create .xlsx files from scratch and both can edit existing .xlsm files but I can't find any thing about actually creating a .xlsm file from scratch.
I've looked over the openpyxl documentation here and the xlsxwriter documentation here but I have not been able to find anything about how to create a .xlsm file from scratch. I can't even find anything about how to convert a .xlsx file to an .xlsm file.
The closest I have come is that you can use vba_extract.py which is included in xlsxwriter to extract a file named vbaProject.bin from an existing .xlsm file which you can then add to a .xlsx file and then you can save it as a .xlsm file, but I need to create a .xlsm file from scratch, not relying on having some file there to use to create it.
Is there anything out there with openpyxl, xlsxwriter or any other utility I can use with python 3.8.1 to create a .xlsm file from scratch without having to depend on having an existing .xlsm or a vbaProject.bin file? As always a correct, clearly explained answer will be marked as accepted and will be upvoted.
It appears as though an XLSM file is a zipped Excel file, containing macros, etc.
I was able to find a potential fix here using openpxyl:
import openpyxl as px
from openpyxl import load_workbook
import os
...
wbname='orig_fname.xlsm'
wb = load_workbook(filename=wbname, keep_vba=True)
...
wb.save('temp.xlsm')
os.rename('temp.xlsm', wbname)
Please let me know if it works for you.
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')
To the best of my knowledge, xlsm format is essentially the same as xlsx, with the only difference that it can contain macroses (see https://wiki.fileformat.com/spreadsheet/xlsm/ for instance).
So, you can use openpyxl (https://openpyxl.readthedocs.io/en/stable/), and you don't need to do anything else other than saving your xlsx file with the xlsm extension (code is taken from the official doc and changed slightly):
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
wb.save('new_document.xlsm')
However, there's a strange behaviour in openpyxl which is not documented properly, so to make file actually openable in the Excel you will also need:
wb = load_workbook('new_document.xltm', keep_vba=True)
wb.save('new_document.xlsm')
There's a little bit of explanation for this here: https://openpyxl.readthedocs.io/en/stable/tutorial.html#saving-as-a-stream
So the complete code:
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A1'] = 42
ws.append([1, 2, 3])
wb.save('new_document.xlsm')
# and the workaround
wb = load_workbook('new_document.xltm', keep_vba=True)
wb.save('new_document.xlsm')
