Forum Discussion

harmatii's avatar
harmatii
Occasional Contributor
5 years ago

How to select Excel cells by cell name (ex: E5, B10..)?

Trying to select required cells in Excel using TestComplete, and it locates them by position on the screen:

#Clicks the 'Book1' object.
Aliases.EXCEL.wndXLMAIN.XLDESK.Book1.Click(264, 131)
#Clicks the 'Book1' object.
Aliases.EXCEL.wndXLMAIN.XLDESK.Book1.Click(69, 248)

 

Is there a way to locate them by their name (ex: E5, B10..)?

  • BenoitB's avatar
    BenoitB
    5 years ago

    My solution was using COMobject and not gui so it wont work directly like that in gui.

     

    By GUI to select a cell you have just to enter its address in the highligthed edit box below :

     

  • BenoitB's avatar
    BenoitB
    Community Hero
    5 years ago

    To select cells use Range

    let range1 = excelMainSheet.Range(excelMainSheet.Cells.Item(4, 8), excelMainSheet.Cells.Item(5, 9));

     

    To select individually use Cells.Item(L,C)

    Cells.Item can be used in both syntax Row and Col index  or Row and Col name

    For example this put in cell J4 the address of itself (here $J$4),  you see that i use both syntax.

    excelMainSheet.Cells.Item("4","J").Value2 = excelMainSheet.Cells.Item(4, 10).Address();

     

    Here excelMainSheet is your active sheet.

     

    • harmatii's avatar
      harmatii
      Occasional Contributor
      5 years ago

      Tried that but getting "Unable to find the object Cells" error

       

      here is my code: 

       

      def Test1():
      #Runs the "EXCEL" tested application.
      TestedApps.EXCEL.Run(1, True)
      #Maximizes the 'wndXLMAIN' window.
      Aliases.EXCEL.wndXLMAIN.Maximize()
      OCR.Recognize(Aliases.EXCEL.wndXLMAIN.FullpageUIHost.NetUIHWND).BlockByText("Blank workbook").Click()
      #Clicks the 'Book1' object.
      Aliases.EXCEL.wndXLMAIN.XLDESK.Cells.Item("4","J").Click()

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        5 years ago

        Question:

        May I ask why you're using the UI of Excel to automate manipulating an Excel workbook/spreadsheet?

        You can do a LOT more without having to mess around with identifying ribbon controls, onscreen objects, etc., but simply instantiating an Excel.Application object using Sys.OleObject and utilizing the methods and properties of Excel, Excel Workbooks, and Excel Sheets directly without having to work with the UI at all.