Why Doesn't Importing openpyxl Save as .xlsm?

The core reason why importing data with openpyxl doesn't automatically save your Excel file as a .xlsm file (macro-enabled workbook) is that openpyxl, by default, focuses on handling the underlying data and workbook structure, not specific file formats or the presence of macros.

Here's a more detailed explanation:

1. Openpyxl's Focus:

- openpyxl is a Python library primarily designed for reading and writing Excel files in the .xlsx format. It manages worksheets, cell values, styles, and other structural components. It does not inherently recognize or preserve VBA macros or file-format specific settings like .xlsm.

2. .Xlsx vs .Xlsm:

- The .xlsx format is a standard XML-based file format that stores spreadsheet data without any macro code. The .xlsm format, on the other hand, is identical to .xlsx but includes support for storing VBA (Visual Basic for Applications) macro code within it.

3. Macro Handling:

- When you modify an .xlsm file using openpyxl and save it, it effectively saves it as an .xlsx file. Openpyxl doesn’t process or keep the embedded VBA code or macro settings that make a file an .xlsm. Instead, it focuses on data and spreadsheet structure, stripping those features.

4. The Issue of Template and Data:

- If you are aiming to retain macros while manipulating the workbook content, you can use openpyxl to read existing .xlsm file, then save as a new .xlsm file and overwrite existing data.

5. Saving a New .xlsm:

- Openpyxl is not designed to programmatically generate or migrate macro code. If your starting point is an .xlsm file, and you need to retain those macros, saving as new .xlsx with openpyxl will lose the macros.

6. Example of the Issue:

import openpyxl

# Load an existing .xlsm file
workbook = openpyxl.load_workbook('my_file.xlsm')

# Manipulate some data
sheet = workbook.active
sheet['A1'] = 'New Value'

# If you try to save as a .xlsm
workbook.save('new_file.xlsm')

# The result is a .xlsm file without the macros, and you should have just saved as .xlsx file

In summary, openpyxl is an excellent library for handling Excel data, but it does not manage macros. When working with .xlsm files, you should be aware that openpyxl might remove macros when saving, and that you should save as .xlsx. To work effectively with .xlsm files you need to start with a .xlsm file.

Learn more about this