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.
解决方案
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')