pythonexcelopenpyxlhiddenxlsm

Xlsm file Python

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.

Solution

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