Forum Discussion

matt_chambers's avatar
matt_chambers
15 years ago

Writing table checkpoint data to Excel

Hello,



I am new to TestComplete and I was wondering if there was a way to write the data from a Table Checkpoint into Excel. I ask because we are using an Infragistics grid that dynamically names fields upon calculation, so attempting to pull the data from individual fields doesn't work for some fields. The table checkpoint; however, finds all of the fields and associated data and I would like to write all of the data to an Excel file to use later in the test as part of a data driven loop to verify that the calculations across multiple points is correct by feeding them into an outside calculation engine. Any information is greatly appreciated.



V/R

Matt

1 Reply

  • Abramova's avatar
    Abramova
    Icon for Staff rankStaff
    15 years ago

    Hello,


    Yes, there is a way to copy data retrieved by the Table checkpoint into an Excel document.


    When you create a table checkpoint, TestComplete retrieves data from a tabular control and stores it to the Table element of the Stores | Tables collection in your project.  To access the data stored in the Table element, you can use the Table program object. Using the Table object's properties and methods, you can access the stored values.


    As for working with Excel files, you can access them from scripts via the Excel.Application COM object.  To retrieve a reference to this object, use the Sys.OleObject property. For more information about the Excel OLE object model, see the Excel Object Model Overview article in the MSDN library.


    To learn how to write data to an Excel file from TestComplete scripts, see http://smartbear.com/support/viewarticle/20878/.


    So, you can read data from the Table element by using the Table program object and write it to the Excel file by using the Excel. Application COM object. Below, is a sample script that demonstrates how you can do this. This script retrieves the data from the Table1 element of the Stores | Table collection, copies the data to a new Excel document and saves the document as d:\MyStorage.xlsx.




    Sub WriteDataToExcel

      'Specifies the data returned by the table checkpoint

     
    Set MyTable = Tables.Table1



      'Creates a new Excel file

      Set Excel = Sys.OleObject("Excel.Application")

      Delay 3000 ' Wait until Excel starts

      Excel.Visible = True

     
    Excel.Workbooks.Add



      'Copies data from the table to the Excel document

      For i = 0 to MyTable.RowCount-1

        s = ""

        For j = 0 to MyTable.ColumnCount-1

     'Take notice of the row and column indexes 

          Excel.Cells(i+1, j+1) = MyTable.Values(i,j)

        Next

     
    Next



      'Saves the Excel document

      Excel.ActiveWorkbook.SaveAs "d:\MyStorage.xlsx"

      Excel.Quit

    End Sub


    Note that when you access to the Excel document cells, the row index and column index both start from 1, meaning that cell A1 will be Excel.Cells(1, 1), and so on. In the Table element, the row and column indexes are zero-based - that is, they start from 0.