Cannot select specific excel sheet in Python with OLE Object

[tc_2018]

Hi,

I followed the link below but not successful when trying to open an excel file with Sheet2 enable.

https://community.smartbear.com/t5/TestComplete-General-Discussions/Having-Trouble-switching-to-diff...

My Codes:

def openExcel():
    Log.Message(os.getcwd())
    sheet1 = "Testcase1"
    sheet2 = "Testcase2"
    fileName = "C:\\Works\\myTestcases.xlsx"

    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName)
        mySheet = workBook.Sheets.Item["sheet2"]

    except (RuntimeError):
        Log.Warning("Worksheet " + sheet2 + " was not found.");

Result:

Got the warning result saying: "Worksheet Testcase2 was not found.

I don't know what I did wrong. Please shed some light! Thanks.

[tristaanogre]

Change the following line

mySheet = workBook.Sheets.Item[sheet2]

sheet2 is the variable name so it should not be listed in quotes.

[tc_2018]

Now, I don't see any warning. However, when stopping at a break point, I don't see "sheet2" gets select and active. And all the info printed out are from "sheet1".

So sheet2 still not be selected, and its info still not being able to printout. Please help again!

def openExcel():
    Log.Message(os.getcwd())
    sheet1 = "Testcase1"
    sheet2 = "Testcase2"
    fileName = "C:\\Works\\myTestcases.xlsx"

    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName)
        mySheet = workBook.Sheets.Item[sheet2]

    except (RuntimeError):
        Log.Warning("Worksheet " + sheet2 + " was not found.")

    RowCount = excel.ActiveSheet.UsedRange.Rows.Count
    ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
    Log.Message("Row Count = " + VarToStr(RowCount))
    Log.Message("Col count = " + VarToStr(ColumnCount))
    cell = VarToStr(excel.Cells.Item[2, 1])
    Log.Message(cell)
    excel.Quit() 

[tristaanogre]

Well, you set mySheet to = sheet2 but you haven't indicated that sheet2 is your active sheet.  That's something you need to do first before you can use "Activesheet"

To do that, before you start working with the sheet, call

mySheet.Activate()

[tc_2018]

I try below per your suggestion and sheet2 still not be able to be selected.

    mySheet.Activate

    # OR mySheet.Select 

    RowCount = excel.ActiveSheet.UsedRange.Rows.Count
    ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
    Log.Message("Row Count = " + VarToStr(RowCount))
    Log.Message("Col count = " + VarToStr(ColumnCount))

[tc_2018 · Accepted solution]

It took time and I finally found the solution.

To select a specific sheet of an excel file, an Activate method must be called. Per Microsoft doc: "Calling this method is equivalent to clicking the sheet's tab".

My working codes is below:

def openExcel():
    fileName = "C:\\Works\\Testcases.xlsx"
    excel = Sys.OleObject["Excel.Application"]
    excel.Visible = True
    try:
        workBook = excel.Workbooks.Open(fileName) 
    except (RuntimeError):
        Log.Warning("File " + fileName + " was not found.");
    numFound = workBook.Sheets.Count
    Log.Message("This workbook has " + str(numFound) + " worksheets.")
    for i in range(0,numFound):
        sheetName = workBook.Sheets.Item[i+1].Name
        Log.Message(str(sheetName))
        excel.Worksheets.Item[sheetName].Activate() #Clicking the tab with Activate Method
        RowCount = excel.ActiveSheet.UsedRange.Rows.Count
        ColumnCount = excel.ActiveSheet.UsedRange.Columns.Count
        Log.Message("Row Count = " + VarToStr(RowCount))
        Log.Message("Col count = " + VarToStr(ColumnCount))
        cell = VarToStr(excel.Cells.Item[2, 1])
        Log.Message(cell)
    excel.Quit()