Writing table checkpoint data to Excel

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.