Search code examples
pythonexcelvbacompywin32

Reading large excel tables (guessing size)

I'm using pywin32 to read and write to excel. Now the only method I know is accessing Range.Value however, I usually don't know the size of the full excel table in advance. So at the moment I read line by line until I find a completely empty line. This can be quite slow.

Of course I will try tricks like reading blocks of data - then I'd have to find an optimal block size.

Do you know another method (maybe some internal excel function) or other approaches which are faster?

Solution

  • In VBA, we often use the End statement like this:

    Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

    That may help you find the last used cell of a column (yet, I don't know how to extend this method to pywin)