How to save XLSM file using macro using openpyxl

Question

I have a .xlsm file with a macro function. I load it with openpyxl and write some data to a file and finally want to save it as another .xlsm file.

To save the file as an XLSM file, I used the code below in my Python script.

wb.save('testsave.xlsm'); 

But I can not open this file if I saved as above. But if I saved it as .xlsx , then I can open the file without the macro function that the original file had.

I want to open an Excel worksheet with a macro function, edit the file and save it as a new .xlsm file using openpyxl . How can i do this?

Answer 1

For me, this worked with openpyxl==2.3.0-b2

 wb = load_workbook(filename='original.xlsm', read_only=False, keep_vba=True) .. wb.save('outfile.xlsm') 

The documentation also mentions the following: http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

Answer 2

I do not know how relevant this is for the person who asked the question, but I am dealing with the same problem and found a possible solution.

1) open the source file (say: 1.xlsm) and do the magic with openpyxl

2) save as 2.xlsx

3) both files are actually archived files: extract them to temporary directories

4) copy the files from the source file directory to the xlsx directory: one of the files is a macro (vbaProject.bin), and 2 files are necessary because they describe the file type among other things

5) put all the files belonging to the xlsx file back into the zip file, and rename it from zip to xlsm. This file contains the original macro and was edited using openpyxl

Optional: 6) delete two temporary directories and a 2.xlsx file

Code example:

 import openpyxl import zipfile from shutil import copyfile from shutil import rmtree import os PAD = os.getcwd() wb = openpyxl.load_workbook('1.xlsm') ##### # do magic with openpyxl here and save ws = wb.worksheets[0] ws.cell(row=2, column=3).value = 'Edited' # example ##### wb.save('2.xlsx') with zipfile.ZipFile('1.xlsm', 'r') as z: z.extractall('./xlsm/') with zipfile.ZipFile('2.xlsx', 'r') as z: z.extractall('./xlsx/') copyfile('./xlsm/[Content_Types].xml','./xlsx/[Content_Types].xml') copyfile('./xlsm/xl/_rels/workbook.xml.rels','./xlsx/xl/_rels/workbook.xml.rels') copyfile('./xlsm/xl/vbaProject.bin','./xlsx/xl/vbaProject.bin') z = zipfile.ZipFile('2.zip', 'w') os.chdir('./xlsx') for root, dirs, files in os.walk('./'): for file in files: z.write(os.path.join(root, file)) z.close() #clean os.chdir(PAD) rmtree('./xlsm/') rmtree('./xlsx/') os.remove('./2.xlsx') os.rename('2.zip', '2.xlsm') 

Answer 3

That's right, openpyxl cannot read and write VBA code.

According to this thread :

I think you should not specify the xlsM extension, because the file will not contain VBA code. openpyxl is only used to create xlsX files.

Try this fork instead: if you pass the keep_vba=True parameter to load_workbook , it should do the job.

Hope this helps.

Answer 4

I had the same problem when editing xlsm files using openpyxl. I have tried most of the solutions / workarounds available on stackoverflow and other forums. But none of them worked. Then I found xlwings , this Python library processes the xlsm document, it saves all macros.

 import xlwings as xw wb = xw.Book('macro_xl.xlsm') sheet = wb.sheets['Sheet1'] sheet.range('A1').value = 'From Script' wb.save('result_file_name.xlsm')