CODEWITHSUNDEEP
pythonopenpyxlxlsm
AnujAroshA
AnujAroshA

Reputation: 4811

How to save XLSM file with Macro, using openpyxl

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?

Upvotes: 32

Views: 90206

Answers (7)

Gastao Dias Motta
Gastao Dias Motta

Reputation: 1

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.

Upvotes: -1

Abdullokh Alimov
Abdullokh Alimov

Reputation: 61

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

import xlwings as xw

wb = xw.Book(DATA.xlsm)

Upvotes: 0

Joost
Joost

Reputation: 490

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

Upvotes: 9

Michel Kluger
Michel Kluger

Reputation: 173

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)

Upvotes: 0

John Prawyn
John Prawyn

Reputation: 1663

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

Upvotes: 7

eule
eule

Reputation: 649

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

Upvotes: 51

alecxe
alecxe

Reputation: 473863

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.

Upvotes: 2

Related Questions