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