Why do I get an enumeration error using xlwings?

Question

I'm using xlwings and when I try to open the excel workbook I get a strange error. It seems to come from the xlwings source code so I don't know what to do. Does somebody have an idea of how to solve this error ? I'm using xlwings version 0.27.6.

Here is the error :

File "<string>", line 1, in <module>

  File "u:\user\python\test.py", line 22, in <module>

    wb = xw.Book('ExcelSheetName.xlsm')

  File "C:\Users\user\Anaconda3\lib\site-packages\xlwings\main.py", line 821, in __init__

    for wb in app.books:

  File "C:\Users\user\Anaconda3\lib\site-packages\xlwings\main.py", line 67, in __iter__

    for impl in self.impl:

  File "C:\Users\user\Anaconda3\lib\site-packages\xlwings\_xlwindows.py", line 755, in __iter__

    for xl in self.xl:

  File "C:\Users\user\Anaconda3\lib\site-packages\xlwings\_xlwindows.py", line 231, in __iter__

    for v in self._inner:

  File "C:\Users\user\Anaconda3\lib\site-packages\win32com\client\dynamic.py", line 257, in __getitem__

    raise TypeError("This object does not support enumeration")

TypeError: This object does not support enumeration

It seems that it does not support enumeration on app.books... Any idea why this code fails? Thanks in advance for your help

UPDATE

It seems to happen when several instances of Excel are opened at the same time...

What I'm trying to do is to take the data from an Excel file, make some computations in Python and return the results in the same Excel file. The Python file .py and the Excel file .xlsm are in the same directory. The error I get happens very 'early' in the code, which is the following :

 import pandas as pd
 import numpy as np
 import xlwings as xw
    
 wb = xw.Book('ExcelSheetName.xlsm')

Answer 1

I finally found in xlwings documentation an alternative way to connect Python and Excel that seems to work and to solve the problem I described above.

Suppose you have a file called 'ExcelSheetName.xlsm' with a sheet called 'Sheet1'. You can use the following code:

import xlwings as xw

def my_macro():
    wb = xw.Book.caller()
    return wb

if __name__ == '__main__':
    xw.Book(r 'U:\path\ExcelSheetName.xlsm').set_mock_caller()
    wb = my_macro()
else :
    wb = my_macro()

Sheet1 = wb.sheets['Sheet1']

With this code I don't get the enumeration error anymore! Moreover, I can easily run the code for debugging from Python or from Excel via RunPython without having to change the source code.