tc_2018
6 years agoContributor
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...
- 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()