Forum Discussion

andrew_pitt's avatar
andrew_pitt
New Contributor
12 years ago

Best way to control Excel?

Hi All,



I am after suggestions for the best way to control Excel using TestComplete.



I have a .NET application that launches Excel with a macro loaded. I then need to modify the data in excel, save, then confirm that the data modified in Excel is reflected in the .NET application



The scenario is as follows:



1. (while .NET application is open) Click "open excel" button (in the application) -> Excel loads

2. open customomers .xls file -> File is open

3. Click on View -> Macros -> Select desired Macro and press Run

4. Confirm macro changes have happened

5. Close excel and confirm changes are reflected in the new application.



The part of my code is as follows:



Aliases.CATSV3.ExcelBooksGridForm1.openExcelButton.ClickButton();

 

Aliases.Excel_popup.Excel_popup.panel.toolbarRibbon.panelRibbon.panel.propertypageRibbon.buttonFileTab.ClickButton();

Aliases.Excel_popup.Excel_popup.panel2.pane.paneBackstageView.client.menubarFile.menuitemOpen.Click();

 

Aliases.EXCEL.dialogOpen.panelFileName.comboboxFileName.editFileName.SetText("C:\\automation\\files\\temp\\STAFFING MODEL XLS.xls");

 

Aliases.EXCEL.dialogOpen.splitbuttonOpen.Click();

 

Aliases.EXCEL.dialogMicrosoftExcel.buttonYes.Click();

    

Aliases.Excel_popup.Excel_popup.panel1.panelStaffingModelXlsCopyXls.clientSheetNavigationBar.tablistSheetTabs.ClickTab("Project Input Output Sheet");



so I am stuck trying to find out the best way to proceed.



- Using TestComplete to control Excel, I can navigate through all the Excel tabs, ribbons, file processes, etc but I cannot work out how to enter/modify data in the cells and count the columns/rows.



- Using the Sys.OleObject method, I can open up an existing Excel file or create a new one, but I cannot work out how to get TestComplete to "hook" into the already launched Excel document.



Any suggestions?



Thanks,

Andrew
  • hlalumiere's avatar
    hlalumiere
    Regular Contributor
    12 years ago
    For UI, just use the TestComplete objects as usual. If you need to play with the data in a workbook, use the Excel interop.



      ' Obtain a reference to the already running Excel, or start a new instance if none is running.

      On Error Resume Next

      Set objExcel = GetObject( , "Excel.Application")

      If Not Err.Number = 0 Then ' If there was an error, there is no existing instance, create one.

          Set objExcel = CreateObject("Excel.Application")

      End If : Err.Clear : On Error Goto 0

     

      ' Get the value of the cell @ Row, Col in the first worksheet of the first workbook of the application instance.

      CellValue = objExcel.Workbooks(0).Worksheets(0).Cells(Row, Col).Value



  • andrew_pitt's avatar
    andrew_pitt
    New Contributor
    12 years ago


    Hi Hugo,



    Thanks for the response.



    Do you have the equivalent in javascript as well? I haven't had any luck converting it from vbscript yet.



    Thanks,

    Andrew