Python - win32com.client: pywintypes.com_error when trying to open a (large) Excel

Question

I´m trying to use the win32com.client to open a password protected excelfile.
The file has 37 columns and 28.140 rows.

I need to keep the number of rows dynamic as future files will have a different number of rows (both more or less). I found a solution here on SO that worked fine so far with smaller files.

This is my code:

import win32com.client as w3c
xlApp = w3c.Dispatch("Excel.Application")
password = 'Greatpassword'
path = 'filepath'
xlwb = xlApp.Workbooks.Open(path,False, True, None, Password=password)
xlws = xlwb.Sheets[0]


max_clm = 37

xlUp = -4162
max_row = xlws.Cells(xlws.Rows.Count, "A").End(xlUp).Row
print(max_clm, max_row)

data = list(xlws.Range(xlws.Cells(1, 1), xlws.Cells(max_row, max_clm)).Value)


df = pd.DataFrame(data)
df.columns = df.iloc[0]
df = df.drop(0)

xlwb.Close()
xlApp.Quit()

Here is the error message I get:

  File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\site-packages\win32com\client\dynamic.py", line 516, in __getattr__
ret = self._oleobj_.Invoke(retEntry.dispid,0,invoke_type,1)
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352566), None)

With "win32api.FormatMessage(-2147352566)" I get the text which says:
"Out of present range."

What does this error message mean and how do I deal with it?

I re-checked my file to make sure it has the # of rows (variable max_row) and # of columns (variable max_clm).

print(max_clm, max_row)

Gives me 37, 28.140

Thanks in advance!
BR,
MG

Answer 1

So apparently it didn´t work because there were a lot of "blank rows" in the excel file.

So I copy & pasted the real rows to a new file and suddenly it worked. I cannot make sense out of it as the max_row according to my code was 28.140.

Can someone explain this to me?
Is there are maximum number of rows allowed for win32?