Forum Discussion

Vicky_17's avatar
Vicky_17
Occasional Contributor
4 years ago
Solved

How to read a range of excel cells as List or List of List Python using Excel OLE Objects?

I am migrating the existing vba code to py. so we are using the ole object to read excel like below.

 

can someone help to read range of cells, the documentation points to read only a single value instead of range. https://support.smartbear.com/testcomplete/docs/reference/test-objects/members/sys/oleobject-property-sys-object.html

 

Even i tried to download additional samples and that also don't have any range function. i need a good documentaion for excel using ole


Excel = Sys.OleObject["Excel.Application"]
Excel.Workbooks.Open("C:\\MyFile.xlsx")

RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count

for i in range(1, RowCount + 1):
    s = "";
    for j in range(1, ColumnCount + 1):

         ## Is there anyway to read range or all content of the sheet to list of list?
        s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n' 
    Log.Message("Row: " + VarToString(i), s);

Excel.Quit();