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-switching-to-different-sheet-in-excel-using-sys/m-p/166322#M30796

 

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.

 

  • 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()
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    6 years ago

    Change the following line

     

    mySheet = workBook.Sheets.Item[sheet2]

     

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

    • tc_2018's avatar
      tc_2018
      Contributor
      6 years ago

      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's avatar
        tristaanogre
        Esteemed Contributor
        6 years ago

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