Forum Discussion

tc_2018's avatar
tc_2018
Contributor
6 years ago

Cannot select specific excel sheet in Python with OLE Object

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-s...
  • tc_2018's avatar
    tc_2018
    6 years ago

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