How to save XLSM file with Macro, using openpyxl

Question

I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file.

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

wb.save('testsave.xlsm');

But I cannot open that file if I saved as above. But if I saved it as .xlsx then I can open the file without the Macro function that original file had.

I want to open a Excel sheet that has Macro function, edit the file and save it as new .xlsm file using openpyxl. How can I do that?

Oarfish

Answer 1

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

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

It is also mentioend in the documentation here: http://openpyxl.readthedocs.org/en/latest/usage.html?highlight=keep_vba#write-a-workbook-from-xltm-as-xlsm

Dupondius

Is it possible to call and run the macro from python thereafter? – Thurstan

If you have MS Office installed and running Windows then you can run it with the OLE api. Otherwise you have to re-engineer the macro code with openpyxl in Python. – Dupondius

This link does not go to anything related to xlsm! In fact, if you go to openpyxl.readthedocs.io/en/latest/usage.html?highlight=xlsm You'll see, nothing is higlighted, since there is no anything about xlsm. – Earldom

I think they removed that section about xlsm now, but still mention that the file type is supported. Important seems to set the keep_vba flag explicitly as it defaults to false. openpyxl.readthedocs.io/en/latest/usage.html?highlight=keep_vba – Dupondius

This method does not always keep the ActiveX components like the buttons connected to the code. Might be the same as the openpyxl bug of buttons being converted to images. – Dunstan

worked for me, macros are there, but buttons did not stay. – Passenger

The latest version did not work for me, but 2.3.0 worked. – Findley

Works for me with Version 3.1.2. And it's mentioned in the documentation: openpyxl.readthedocs.io/en/3.1.2/… – Molliemollify

Answer 2

I don't know if this is still relevant for the person that asked the question, but I am dealing with the same problem and found a possible solution.

1. open the original file (say: 1.xlsm) and do magic with openpyxl;
2. save as 2.xlsx;
3. both files are actually zipped files: extract them to a temporary directory;
4. copy files from the directory of the original file to the directory of the xlsx files: one of the files is the macro (vbaProject.bin) and 2 of the files are necessary because they describe the type of the file among other things;
5. put all of the files that belong to the xlsx directory back into a zip file and rename this from zip to xlsm. This file contains the original macro and has been edited with openpyxl;
6. (Optional) delete the two temporary directories and the 2.xlsx file.

Example code:

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')

Dissected

This solution is closest that I found, but still, I am getting some errors on file, that excel tries to recover. During this recovery, it (sometimes, not always) mess up the macros! – Earldom

@Dissected Is there anyway to keep the button graphic that control the macros? This script definitely maintains the macros VBA code, but the buttons that were in my other sheets to run the the code are gone. Here are my files including the input file, the output file and the script drive.google.com/file/d/1LOhUqAdjUWQFxCnZMRrdK8Iz4gRMACkw/… . Maybe if you have time you could take a look – Felt

Answer 3

I had the same issue when editing xlsm files using openpyxl. I tried most of the solutions/workarounds available in stackoverflow and in other forums. But none of them worked. Then I found xlwings, this python library handles xlsm document, it preserve all the 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')

Sergius

Error : TypeError: 'Book' object is not subscriptable – Monoceros

it worked for me. try this if approach above was of no success – Pistoleer

Of note, potentially a good solution but doesn't work on Linux as XLWings doesn't work there. – Scratchboard

@T.Shaffner yes... it needs an installation of Excel (and there is none on Linux) – Suffumigate

Thank you, thank you, thank you! Super useful for programs that do a lot of work in Excel. – Vitrify

Answer 4

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

According to this thread:

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

Give a try to this fork instead: if you pass keep_vba=True parameter to load_workbook it should do the job.

Hope that helps.

Cheng

It didn't work for me. Here is my code for that wb = load_workbook('template.xlsm', keep_vba = True); – Oarfish

If this fork doesn't work - then, there is no way to make it work using openpyxl. – Cheng

Cant find that fork but there is bitbucket.org/amorris/editpyxl/src/master – Stomatology

Answer 5

if your initial excel was created with python you might need to add the workbook.xml as well and parse the sheet xlms:

for sheet in range(1,4):
    with open('sheetX.xml', 'r') as myfile: 'r') as myfile:
        my_str=myfile.read()

substr = "<dimension ref="
inserttxt = "<sheetPr codeName=\"Sheet"+str(sheet)+"\"/>"

idx = my_str.index(substr)
my_str = my_str[:idx] + inserttxt + my_str[idx:]

with open('sheetX.xml', "w") as text_file:
    text_file.write(my_str)

Deferential

Answer 6

it's better to use xlwings to work with xlsm documents. I have tested it.

import xlwings as xw

wb = xw.Book(DATA.xlsm)

Johanajohanan

Answer 7

Regarding using xlwings to save a xlsm file with macro (see answer of John Prawyn), you can also export a dataframe (df) to an existing sheet.

Example code:

import xlwings as xw
import pandas as pd

df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b']) # Here you have a dataframe.

wb = xw.Book('FileName.xlsm') # Connect to a xlsm file that is open or in the current working directory.
sheet = wb.sheets['Sheet1'] # Instantiate a sheet object that already exists in xlsm file ('Sheet1').

sheet['A1'].value = df # Export dataframe values to 'Sheet1' and preserve the macros.

Unreasonable

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Luralurch

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Hello