Xlsm file Python

Question

I want to open an .xlsm file with openpyxl library. When I tried to open it, it gives me the IndexError: index out of range. I also observed if I delete all the hidden worksheets in the file before running the code it will be OK. Can you help me? Because I don't want to modify all the files with this problem

I tried to modify read_only and keep_vba to True but doesn't work.

Answer 1

Openpyxl does not support .xlsm files, one option is to use win32com.client if you have Excel installed. To save you some time you will need to pip install pywin32 in order to access this library.

Here is an example of opening a file.

import win32com.client
o = win32com.client.DispatchEx("Excel.Application")

# Run in background, if application fails then excel may not close properly.
o.Visible = False
o.ScreenUpdating = False
o.Interactive = False

wb = o.Workbooks.Open('test.xlsm') # enter full path
ws = wb.Worksheets(1) # sheet number starting at 1
ws.cells(1, 1).value = 'test'

o.DisplayAlerts = False # automatically close save prompt.
wb.SaveAs(Filename='test.xlsm') # enter full path
o.DisplayAlerts = True
wb.Close(True)

Answer 2

The problem you are facing is due to the fact that the openpyxl library does not fully support macros and VBA (Visual Basic for Applications), which can be included in the .xlsm file. This can lead to errors when trying to open such files.

If you only need to read data from a file .xlsm without executing macros, you can try using the data_only=True attribute, which will allow you to open the file without processing macros. Here's how it might look in the code:

import openpyxl

wb = openpyxl.load_workbook('your_file.xlsm', data_only=True)

However, if macros are important to you and you don't want to delete them, you may have to look for alternative libraries that better support macros in .xlsm files. For example, you might consider using the xlwings or pywin32 libraries, which provide access to Excel functionality, including macros.