A.: Yes, you can. There are several ways to do this:

[VBScript]

Dim Con
Set Con = Sys.OleObject("ADODB.Connection")
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;"+"Data Source=D:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb"
Con.Open
. . .

[JScript]

var Con = Sys.OleObject("ADODB.Connection");
Con.ConnectionString ="Provider=Microsoft.Jet.OLEDB.3.51;" + "Data Source=C:\\Program Files\\Microsoft Visual Studio\\VB98\\biblio.mdb";
Con.Open;
. . .

[DelphiScript]

var Con: OleVariant;
. . .
Con := Sys.OleObject('ADODB.Connection');
Con.ConnectionString :='Provider=Microsoft.Jet.OLEDB.3.51;' + 'Data Source=C:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb';
Con.Open;
. . .

[C++Script, C#Script]

var Con = Sys["OleObject"]("ADODB.Connection");
Con["ConnectionString"] ="Provider=Microsoft.Jet.OLEDB.3.51;" + "Data Source=C:\\Program Files\\Microsoft Visual Studio\\VB98\\biblio.mdb";
Con["Open"];
. . .

In general, you can work with any database by setting up a corresponding DSN and using the ADO plug-in. For example, in the Control Panel | Administrator Tools | Data Source (ODBC) dialog, create a new DSN based on the "Microsoft Access Driver (*.mdb)" driver and link this DSN to the Biblio.mdb database that comes with Microsoft Visual Basic. Name the DSN as "MyDSBiblio" and run the following script to see how to work with the database via the ADO plug-in:

[VBScript]

' The TestDB routine posts all authors to the test log
' whose birth year is specified in the database
Sub TestDB
  ' Creates a new connection
  Set ConnDB = ADO.CreateConnection
  ConnDB.ConnectionString = "DSN=MyDSBiblio"
  ConnDB.Open

  ' Opens a recordset
  Set Tbl = ConnDB.Execute("SELECT * FROM Authors " + "WHERE [Year Born] IS NOT NULL")

  ' Scans all records returned by the query
  Tbl.MoveFirst
  While Not Tbl.EOF
  	' Gets the field values
  	s = Tbl.Fields("Author").Value
  	' Converts Year Born (date value) to string value
  	s2 = CStr(Tbl.Fields("Year Born").Value)
  	' Posts message to the log
  	Log.Message s + " (" + s2 + ")"
  	Tbl.MoveNext
  WEnd

  ' Closes the recordset and connection
  Tbl.Close
  ConnDB.Close
End Sub

[JScript]

// The TestDB routine posts all authors to the test log
// whose birth year is specified in the database
function TestDB()
{
  var ConnDB, Tbl, s, s2;

  // Creates a new connection
  ConnDB = ADO.CreateConnection();
  ConnDB.ConnectionString = "DSN=MyDSBiblio";
  ConnDB.Open();

  // Opens a recordset
  Tbl = ConnDB.Execute("SELECT * FROM Authors " + "WHERE [Year Born] IS NOT NULL");

  // Scans all records returned by the query
  Tbl.MoveFirst();
  while (! Tbl.EOF) 
  {
    // Gets the field values
    s = Tbl.Fields("Author").Value;
    // Converts Year Born (date value) to string value
    s2 = DateToStr(Tbl.Fields("Year Born").Value);
    // Posts message to the log
    Log.Message (s + " (" + s2 + ")");
    Tbl.MoveNext();
  };

  // Closes the recordset and connection
  Tbl.Close();
  ConnDB.Close();
}

[DelphiScript]

// The TestDB routine posts all authors to the test log 
// whose birth year is specified in the database
procedure TestDB()
var  
  ConnDB, Tbl, s, s2 : OleVariant;
begin
  // Creates a new connection
  ConnDB := ADO.CreateConnection;
  ConnDB.ConnectionString := 'DSN=MyDSBiblio';
  ConnDB.Open;
  // Opens a recordset
  Tbl := ConnDB.Execute('SELECT * FROM Authors ' + 'WHERE [Year Born] IS NOT NULL');

  // Scans all records returned by the query
  Tbl.MoveFirst;
  while not (Tbl.EOF) do
  begin
    // Gets the field values
    s := Tbl.Fields('Author').Value;
    // Converts Year Born (date value) to string value
    s2 := DateToStr(Tbl.Fields('Year Born').Value);
    // Posts message to the log
    Log.Message (s + ' (' + s2 + ')');
    Tbl.MoveNext;
  end;

  // Closes the recordset and connection
  Tbl.Close;
  ConnDB.Close;
end;

[C++Script, C#Script]

// The TestDB routine posts all authors to the test log
// whose birth year is specified in the database
function TestDB()
{
  var ConnDB, Tbl, s, s2;

  // Creates a new connection
  ConnDB = ADO["CreateConnection"]();
  ConnDB["ConnectionString"] = "DSN=MyDSBiblio";
  ConnDB["Open"]();

  // Opens a recordset
  Tbl = ConnDB["Execute"]("SELECT * FROM Authors " + "WHERE [Year Born] IS NOT NULL");

  // Scans all records returned by the query
  Tbl["MoveFirst"]();
  while (! Tbl.EOF) 
  {
    // Gets the field values
    s = Tbl["Fields"]("Author")["Value"];
    // Converts Year Born (date value) to string value
    s2 = DateToStr(Tbl.Fields("Year Born")["Value"]);
    // Posts message to the log
    Log["Message"](s + " (" + s2 + ")");
    Tbl["MoveNext"]();
  };

  // Closes the recordset and connection
  Tbl["Close"]();
  ConnDB["Close"]();
}

See the “Working With Databases” help topic for more information. It contains a lot of references to examples that illustrate working with the databases from scripts.

In TestComplete 6, you can use database checkpoints provided by the Stores | DBTables project item to verify that the database contains the appropriate data. These checkpoints allow you to create a baseline copy of a database table's data, view results of an SQL query and store it in your TestComplete project for further use. To compare the actual database data with the baseline copy, you can use the checkpoint’s Compare method. For example, the following code will compare data stored in the DBTable1 project element with the actual data in the corresponding database:

[VBScript]

If Not DBTables.DBTable1.Compare Then
  Log.Error "Comparison failed."
End If

[JScript]

if (! DBTables.DBTable1.Compare() )
  Log.Error ("Comparison failed.")

[DelphiScript]

if not DBTables.DBTable1.Compare then
  Log.Error ('Comparison failed.')

[C++Script, C#Script]

if (! DBTables["DBTable1"]["Compare"]() )
  Log["Error"]("Comparison failed.")

For more information about database checkpoints, please see the “Database Table Checkpoints” help topic.

A.: In DelphiScript, file operations are supported by special file functions similar to those of Pascal (see “DelphiScript - List of Supported Routines” help topic). For VBScript, JScript, C++Script and C#Script the situation is different. These languages do not include functions to write or read data files. The solution, as the examples show, is to create the FileSystemObject via COM and then use this object to work with the file. Another solution is to use functions provided by the Utilities object. The following code gives simple examples of reading and writing files:

[VBScript]

' Reading a file
Sub ReadFile(AFileName)

  ForReading = 1
  ForWriting = 2
  ForAppending = 8

  ' Creates a new file object
  Set FS = Sys.OleObject("Scripting.FileSystemObject") 
  Set F = FS.OpenTextFile(AFileName, ForReading)
  While Not F.AtEndOfStream
    s = F.ReadLine
    Log.Message s
  WEnd
  F.Close
End Sub

' Writing to a file
Sub WriteToFile(AFileName)
  Const ForReading = 1, ForWriting = 2, ForAppending = 8, TristateFalse = 0
  Dim fs, f, s

  s = "Hello, world!"

  ' Creates a new file object
  Set fs = CreateObject("Scripting.FileSystemObject")
  If Not fs.FileExists(AFileName) Then
    Set f = fs.CreateTextFile(AFileName)
  Else
    Set f = fs.OpenTextFile(AFileName, ForAppending, TristateFalse)
  End If
  f.Write s
  f.Close
End Sub

[JScript]

// Reading a file
function ReadFile(AFileName)
{
  ForReading = 1;
  ForWriting = 2;
  ForAppending = 8;

  // Creates a new file object
  fs = Sys.OleObject("Scripting.FileSystemObject");
  f = fs.OpenTextFile(AFileName, ForReading);
  while(! f.AtEndOfStream)
  {
    s = f.RadLine();
    Log.Message(s);
  }
  f.Close();
}

// Writing to a file
function WriteToFile(AFileName)
{
  ForReading = 1;
  ForWriting = 2;
  ForAppending = 8;
  TristateFalse = 0;

  s = "Hello, world!";

  // Creates a new file object
  fs = new ActiveXObject("Scripting.FileSystemObject")
  if(! fs.FileExists(AFileName))
    f = fs.CreateTextFile(AFileName);
  else
    f = fs.OpenTextFile(AFileName, ForAppending, TristateFalse);

  // Writes string to file
  f.Write(s);

  // Closes the file
  f.Close();
}

[DelphiScript]

// Reading a file
procedure ReadFile(AFileName : string);
var
  FileVar, s : OleVariant;
begin
  AssignFile(FileVar, AFileName);
  Reset(FileVar);
  while not Eof(FileVar) do
  begin
    Readln(FileVar, s);
    Log.Message(s);
  end;
  CloseFile(FileVar);
end;

// Writing to a file
procedure WriteToFile(AFileName);
var
  f, s : OleVariant;
begin
  s := 'Hello, world!';

  AssignFile(f, AFileName);
  if Utilities.FileExists(AFileName) then
    Append(f)
  else
    Rewrite(f);
  Write(f, s);
  CloseFile(f);
end;

[C++Script, C#Script]

// Reading a file
function ReadFile(AFileName)
{
  var ForReading, ForWriting, ForAppending;
  var f, fs, s;

  ForReading = 1;
  ForWriting = 2;
  ForAppending = 8;

  // Creates a new file object
  fs = Sys["OleObject"]("Scripting.FileSystemObject");
  f = fs["OpenTextFile"](AFileName, ForReading);
  while(! f["AtEndOfStream"])
  {
    s = f["ReadLine"]();
    Log["Message"](s);
  }
  f["Close"]();
}

// Writing to a file
function WriteToFile(AFileName){

var ForReading, ForWriting, ForAppending, TristateFalse;
var s, fs, f;

  ForReading = 1;
  ForWriting = 2;
  ForAppending = 8;
  TristateFalse = 0;

  s = "Hello, world!";

  // Creates a new file object
  fs = Sys["OleObject"]("Scripting.FileSystemObject");
  if(! fs["FileExists"](AFileName))
    f = fs["CreateTextFile"](AFileName);
  else
    f = fs["OpenTextFile"](AFileName, ForAppending, TristateFalse);

  // Writes string to file
  f["Write"](s);

  // Closes the file
  f["Close"]();
}

A.: Yes. Excel is a COM server, so you can work with it from scripts via the Excel.Application COM object. Please review our sample located in the <TestComplete folder>\Samples\Scripts\MSOffice folder. This sample demonstrates how to work with Excel via a COM object.

You can also find sample scripts that demonstrate how to work with Excel sheets from TestComplete in the following newsgroup threads:

Also, please see the sample that illustrates how to work with Excel in the following TestComplete forum post on the SQAForums portal:

Documentation on the Excel object model can be found in the MSDN Library:

Below there is a simple procedure that reads data from Excel cells and posts them to TestComplete’s log.

[VBScript]

Sub ReadDataFromExcel
  Set Excel = Sys.OleObject("Excel.Application")
  BuiltIn.Delay 3000 ' Wait until Excel starts
  Excel.Visible = True
  Excel.Workbooks.Open "c:\MyFile.xls"

  For i = 1 to 10
    s = ""
    For j = 1 to 5
      s = s + VarToString(Excel.Cells(i, j)) + Chr(13) + Chr(10)
    Next
    Log.Message "Row: " + VarToString(i), s
  Next
End Sub

[JScript]

function ReadDataFromExcel()
{
  var Excel, i, j, s;
  Excel = Sys.OleObject("Excel.Application");
  BuiltIn.Delay (3000); // Wait until Excel starts
  Excel.Visible = true;
  Excel.Workbooks.Open("c:\\MyFile.xls");

  for (i = 1;i<=10; i++)
  {
    s = "";
    for (j = 1;j<=5;j++)
      s = s + VarToString(Excel.Cells(i, j)) + Chr(13) + Chr(10)
    Log.Message("Row: " + VarToString(i), s);
  }
} 

[DelphiScript]

procedure ReadDataFromExcel;
var 
  Excel, i, j, s : OleVariant;
begin
  Excel := Sys.OleObject('Excel.Application');
  BuiltIn.Delay(3000); // Wait until Excel starts
  Excel.Visible := True;
  Excel.Workbooks.Open('c:\MyFile.xls');

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

[C++Script, C#Script]

function ReadDataFromExcel()
{
  var Excel, i, j, s;
  Excel = Sys["OleObject"]("Excel.Application");
  BuiltIn["Delay"](3000); // Wait until Excel starts
  Excel["Visible"] = true;
  Excel["Workbooks"]Open("c:\\MyFile.xls");

  for (i = 1;i<=10; i++)
  {
    s = "";
    for (j = 1;j<=5;j++)
      s = s + VarToString(Excel.Cells(i, j)) + Chr(13) + Chr(10)
    Log["Message"]("Row: " + VarToString(i), s);
  }
} 

If you want to use the Excel sheet to store data for data-driven testing it is more convenient to use the special DDT.ExcelDriver driver object, that hides the actual data storage format and lets you access data using a unified table-like interface. The following code illustrates the use of DDT.ExcelDriver. For further information refer to the "Data-Driven Testing" help topic.

[VBScript]

Dim RecNo
  
' Posts data to the log (helper routine)
Sub ProcessData
  Dim Fldr, i
  
  Fldr = Log.CreateFolder("Record: " + VarToStr(RecNo))
  Log.PushLogFolder Fldr
  
  For i = 0 To DDT.CurrentDriver.ColumnCount - 1
    Log.Message DDT.CurrentDriver.ColumnName(i) + ": " + VarToStr(DDT.CurrentDriver.Value(i))
  Next
  
  Log.PopLogFolder
  RecNo = RecNo + 1
End Sub
  
' Creates the driver (main routine)
Sub TestDriver
  Dim Driver
  
  ' Creates the driver
  Set Driver = DDT.ExcelDriver("C:\MyFile.xls", "Sheet1") 
  
  ' Iterates through records
  RecNo = 0
  While Not Driver.EOF
    Call ProcessData() ' Processes data
    Call Driver.Next() ' Goes to the next record
  WEnd
End Sub

[JScript]

var RecNo;
  
// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;
  
  Fldr = Log.CreateFolder("Record: " + VarToStr(RecNo));
  Log.PushLogFolder(Fldr);
  
  for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++) 
    Log.Message(DDT.CurrentDriver.ColumnName(i) + ": " + VarToStr(DDT.CurrentDriver.Value(i)));
  
  Log.PopLogFolder(); 
  RecNo = RecNo + 1; 
}
  
// Creates the driver (main routine)
function TestDriver()
{
  var Driver;
  
  // Creates the driver
  Driver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1"); 
  
  // Iterates through records
  RecNo = 0;
  while (! Driver.EOF() ) 
  {
    ProcessData(); // Processes data
    Driver.Next(); // Goes to the next record
  }
}

[DelphiScript]

var RecNo;

// Posts data to the log (helper routine)
procedure ProcessData;
var
  Fldr, i : OleVariant;
begin
  Fldr := Log.CreateFolder('Record: ' + VarToStr(RecNo));
  Log.PushLogFolder(Fldr);
  
  for i := 0 to DDT.CurrentDriver.ColumnCount - 1 do
    Log.Message(DDT.CurrentDriver.ColumnName[i] + ': ' + VarToStr(DDT.CurrentDriver.Value[i]));
  
  Log.PopLogFolder;
  RecNo := RecNo + 1;
end;
  
// Creates the driver (main routine)
procedure TestDriver;
var
  Driver : OleVariant;
begin
  // Creates the driver
  Driver := DDT.ExcelDriver('C:\MyFile.xls', 'Sheet1'); 
  
  // Iterates through records
  RecNo := 0;
  while not Driver.EOF do
  begin
    ProcessData; // Processes data
    Driver.Next; // Goes to the next record
  end;
end;

[C++Script, C#Script]

var RecNo;
  
// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;
  
  Fldr = Log["CreateFolder"]("Record: " + VarToStr(RecNo));
  Log["PushLogFolder"](Fldr);
  
  for(i = 0; i < DDT["CurrentDriver"]["ColumnCount"]; i++) 
    Log.Message(DDT["CurrentDriver"]["ColumnName"](i) + ": " + VarToStr(DDT["CurrentDriver"]["Value"](i)));
  
  Log["PopLogFolder"](); 
  RecNo = RecNo + 1; 
}
  
// Creates the driver (main routine)
function TestDriver()
{
  var Driver;
  
  // Creates the driver
  Driver = DDT["ExcelDriver"]("C:\\MyFile.xls", "Sheet1"); 
  
  // Iterates through records
  RecNo = 0;
  while (! Driver["EOF"]() ) 
  {
    ProcessData();    // Processes data
    Driver["Next"](); // Goes to the next record
  }
}

A.: You can make your data pseudo-unique by adding the current date and time or random numbers. You can find examples of creating random data in the following blogs on our community site: