Read only Excel Cells with values python win32com

Question

I have an Excel Document like the following

num value1 value2

1       A      100
2       B      
3       c      300

I want to iterate through value2 for something with a value of over 200, and if it finds a value over 200, print value1. The big thing I'm having an issue with is telling it to stop the for loop once it reaches the end of the cells with text in it.

My loop would ideally be something like this:

while columnA is not empty:
     if value2 > 200:
           print (value1)

a few notes: I'm using win32com. ColumnA will never be blank within my data set. Thank you in advance for any help you can provide!

Edit: I will not always have the same number of rows for each document. I need it to automatically stop. Sorry for not being clearer

Solution

Consider using Excel's object library, specifically its Range Object or Worksheet.Cells Property. Also, usually in Excel VBA, you search the worksheet to find the last row and then loop until you reach it:

Excel worksheet

Python COM Code (using try/except/finally to always release resources regardless of error or not)

import win32com.client as win32

try:
    f = "myWorkbook.xlsx"
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    wb = xl.Workbooks.Open(f)
    ws = wb.Worksheets(1)

    xlUp = -4162
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    for i in range(2,lastrow):
        # LOOP RANGE OBJ
        if ws.Range("C" + str(i)).Value is not None and ws.Range("C" + str(i)).Value > 200:
            print(ws.Range("B" + str(i)).Value)

        # LOOP CELLS OBJ
        if ws.Cells(i,3).Value is not None and ws.Cells(i,3).Value > 200:
            print(ws.Cells(i,2).Value)

    wb.Close(False)
    xl.Quit

except Exception as e:
    print(e)

finally:
    ws = None
    wb = None
    xl = None

Output

c
c

Comments

[nico]

Thank you! This helped me significantly. The "lastrow" code was exactly what I was looking for.

[Emilio M Bumachar]

Could anyone please explain the magic number xlUp = -4162 ? Regardless, this helped me as well, +1.

[Parfait]

@EmilioMBumachar, see Excel constants for XlDirection enumeration.