TestComplete samples (both built-in and additional) are located in the <Users>\Public\Public Documents\TestComplete 14 Samples folder.

Some file managers display the Public Documents folder as Documents.

Sometimes, you may not be able to use the Excel object to work with data stored in Microsoft Excel files. For example, using this approach, you cannot work with ranges of cells or change cell formats. In these cases, you can work with Excel data from scripts via its COM server -- Excel.Application. To retrieve a reference to the Excel.Application COM object, use the Sys.OleObject property or the JavaScript getActiveXObject method.

Sometimes, you may not be able to use DB Table variables or DDTDriver objects to work with data stored in Microsoft Excel files. For example, using these approaches you can only read data from Excel files, but cannot modify them. In these cases, you can work with Excel data from scripts via its COM server -- Excel.Application. To retrieve a reference to the Excel.Application COM object, use the Sys.OleObject property or the JavaScript getActiveXObject method.

For more information about the Excel OLE object model, see the Excel Object Model Overview article in the MSDN library. The following code illustrates how you can read data from Excel cells by using the Excel OLE object and post this data to the TestComplete log:

JavaScript

function ReadDataFromExcel()
{
  let Excel = getActiveXObject("Excel.Application");
  Excel.Workbooks.Open("C:\\MyFile.xlsx");

  let RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;
  let ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;

  for (let i = 1; i <= RowCount; i++)
  {
    let s = "";
    for (let j = 1; j <= ColumnCount; j++)
      s += (VarToString(Excel.Cells.Item(i, j)) + "\r\n");
    Log.Message("Row: " + i, s);
  }

  Excel.Quit();
}

JScript

function ReadDataFromExcel()
{
  var Excel = Sys.OleObject("Excel.Application");
  Excel.Workbooks.Open("C:\\MyFile.xlsx");

  var RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;
  var ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;

  for (var i = 1; i <= RowCount; i++)
  {
    var s = "";
    for (var j = 1; j <= ColumnCount; j++)
      s += (VarToString(Excel.Cells(i, j)) + "\r\n");
    Log.Message("Row: " + i, s);
  }

  Excel.Quit();
}

Python

def ReadDataFromExcel():
  Excel = Sys.OleObject["Excel.Application"]
  Excel.Workbooks.Open("C:\\MyFile.xlsx")

  RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
  ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count

  for i in range(1, RowCount + 1):
    s = "";
    for j in range(1, ColumnCount + 1):
      s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n'
    Log.Message("Row: " + VarToString(i), s);

  Excel.Quit();

VBScript

Sub ReadDataFromExcel
  Dim Excel, RowCount, ColumnCount, i, j, s

  Set Excel = Sys.OleObject("Excel.Application")
  Excel.Workbooks.Open("C:\MyFile.xlsx")

  RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
  ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count

  For i = 1 To RowCount
    s = ""
    For j = 1 To ColumnCount
      s = s & VarToString(Excel.Cells(i, j)) & vbNewLine
    Next
    Log.Message "Row: " & i, s
  Next

  Excel.Quit
End Sub

DelphiScript

procedure ReadDataFromExcel;
var Excel, RowCount, ColumnCount, i, j, s;
begin
  Excel := Sys.OleObject('Excel.Application');
  Excel.Workbooks.Open('C:\MyFile.xlsx');

  RowCount := Excel.ActiveSheet.UsedRange.Rows.Count;
  ColumnCount := Excel.ActiveSheet.UsedRange.Columns.Count;

  for i := 1 to RowCount do
  begin
    s := '';
    for j := 1 to ColumnCount do
      s := s + VarToString(Excel.Cells(i, j)) + #13#10;
    Log.Message('Row: ' + VarToString(i), s);
  end;

  Excel.Quit;
end;

C++Script, C#Script

function ReadDataFromExcel()
{
  var Excel = Sys["OleObject"]("Excel.Application");
  Excel["Workbooks"]["Open"]("C:\\MyFile.xlsx");

  var RowCount = Excel["ActiveSheet"]["UsedRange"]["Rows"]["Count"];
  var ColumnCount = Excel["ActiveSheet"]["UsedRange"]["Columns"]["Count"];

  for (var i = 1; i <= RowCount; i++)
  {
    var s = "";
    for (var j = 1; j <= ColumnCount; j++)
      s += (VarToString(Excel["Cells"](i, j)) + "\r\n");
    Log["Message"]("Row: " + i, s);
  }

  Excel["Quit"]();
}

Samples

TestComplete includes a sample that demonstrates how to work with data stored in Excel files from scripts via the Excel.Application COM object:

<TestComplete Samples>\Common\MSOffice\

Note:

If you do not have the sample, download the TestComplete Samples installation package from the support.smartbear.com/downloads/testcomplete/samples/ page of our website and run it.

To find more samples that demonstrate how to work with Excel data via its COM server in TestComplete, follow the link below:

http://www.sqaforums.com/showflat.php?Cat=0&Number=345470&an=0&page=0&gonew=1#UNREAD

Working With Excel Files via COM

Samples

See Also