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

[Vicky_17]

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-propert...

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();

[ebarbera]

Hi @Vicky_17 -

I would suggest trying to use the excel methods instead of the ole objects - these methods allow you to pull data from a single cell.

Take a look at this information : https://support.smartbear.com/testcomplete/docs/reference/program-objects/excel/index.html

Let me know if this is not an option - or if there was a reason you were looking to use the ole object over the excel object.

Thanks,
Emma

[Vicky_17]

i want to read a range of cell not Single cell

[ebarbera]

 Hello @Vicky_17 - 

I apologize I read that post incorrectly - is the script you have there not working? Or is it the vba script and you are just having trouble with the conversion process? Also - where are you looking to store the data after you read it from the excel sheet. 

Thanks,

Emma

[Vicky_17]

It is taking long time to load data one by one and My PC is hanging and fan is running fast once i click run .It was a python script. i want to store the read data as List of Lists in python

[chriscc]

Hi Vicky,

I’m not well versed in Python, but you should be able to declare the range like this:

     Range = Excel.ActiveSheet.Range("A2:C3") 

So, the sample code would be something like this...changes are in bold.

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

Range = Excel.ActiveSheet.Range("A2:C3"); 

      ##whatever range you need…I used A2 to C3

for i in range(1, Range.Rows.Count + 1):
    s = "";
    for j in range(1, Range.Columns.Count + 1):
        s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n' 
    Log.Message("Row: " + VarToString(i), s);

Excel.Quit();

[sonya_m]

Thanks everyone for the help!

@Vicky_17 have you tried the suggested approach? Did it work? Please let us know!