Retrieving Data From Access Reports

Applies to TestComplete 14.30, last modified on November 21, 2019

Microsoft Access includes a powerful report building engine. However, when you double-click a report in the database window to view it, Access displays the printable version of the report in the Print Preview window. This window displays the report as it will be printed and does not contain any child controls that provide access to report data. So, how do you retrieve the data from the report?

A possible solution is to use the optical character recognition. In this case, you will have to write code for scrolling operations. In addition, the recognition may give incorrect results for wrapped lines.

Another possible solution is to export the report data to a file and then analyze the exported data. This approach is easier to implement and it does not depend on the way the data is displayed (no “text wrapping” problems). Below you will see how you can export a report and obtain data from a file.

In further explanations, we will use the Summary of Sales by Year report from the Northwind database that is shipped with Microsoft Access.

Microsoft Access lets you export a report to files of various formats (snapshot, Microsoft Excel, HTML, RTF and others). We will export the report to an HTML file since it is easier to retrieve summary data from it.

To export the report to the HTML format, follow these steps (you can record a script to automate the exporting actions in the future):

  • Right-click on the report in the Database window and select Export from the context menu. Microsoft Access will display the Export Report dialog, where you can select the file name and format.
  • Specify the desired file name in the File name edit field.
  • Select HTML in the Save as type box.
  • Press Export.
  • Microsoft Access will display the HTML Output Options dialog, where you can specify additional export parameters.
  • Do not change fields in this dialog and press OK.

Microsoft Access will create a new HTML file holding the exported data. Now we can parse the file to obtain the report data and retrieve summary values from it.

To parse the file, we will use the TestComplete Web Testing plugin. This plugin provides access to elements of the web page that is shown in Internet Explorer, Firefox or a WebBrowser control.

To parse the exported data file:

  • Open the HTML file in your Internet browser.
  • Switch to the TestComplete Object Browser panel and explore the page contents.
  • As you can see, the page contains a lot of element names like Item(nn). If you use the DOM model, the object tree will look like this:

To determine the name of the desired web page elements take advantage of the Object Spy window and its target glyph:

  • Click the Display Object Spy button on the Tools toolbar. TestComplete will display the Object Spy window.
  • Drag the target glyph () from the Object Spy window to the web page. While dragging, a red rectangle will appear around windows, controls and web page elements that TestComplete recognizes as objects.
  • Release the mouse button over the Totals for 1996 text. The Object Spy window will display properties and methods of the selected web page element. The Name property will contain the string that lets you address the element in scripts.
  • Similarly you can obtain the scripting name for all summary cells. In our examples they are --

    Item(73)  - Totals for 1996

    Item(154) - Totals for 1997

    Item(213) - Totals for 1998

Now we can write a script that will retrieve data from the page:

JavaScript
JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript

function ParseHTML()
{

  // Obtains the page object
  Browsers.Item(btIExplorer).Run("file:///C:\\Summary of Sales by Year.html");
  let page = Sys.Browser("iexplore").Page("*");

  // Totals for 1996
  let cell = page.contentDocument.all.item(73);// Obtains the page element
                                    // 73 is the index which we obtain through the Object Spy window
  let row = GetRow(cell); // Obtains the table row to which the element belongs
  let s = ProcessData(row);// Retrieves values from the row's cells
  Log.Message(s); // Posts data to the log

  // Totals for 1997
  cell = page.contentDocument.all.item(151);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);

  // Totals for 1998
  cell = page.contentDocument.all.item(208);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);
}

function ProcessData(ARow)
{
  var cell, s;

  // Obtains the value of the first column
  cell = ARow.cells.item(1);
  s = cell.innerText;

  // Obtains the value of the second column
  cell = ARow.cells.item(2);
  s = s + " Orders shipped: " + cell.innerText;

  // Obtains the value of the third column
  cell = ARow.cells.item(3);
  s = s + " Sales: " + cell.innerText;

  return s;
}

function GetRow(AObj)
{
  if (!strictEqual(AObj, null))
  {
    if (equal(AObj.tagName, "TD"))
      return AObj.parentElement;
    else
      return GetRow(AObj.parentElement);
  }
  else
    return null;
}

JScript

function ParseHTML()
{

  // Obtains the page object
  Browsers.Item(btIExplorer).Run("file:///C:\\Summary of Sales by Year.html");
  var page = Sys.Browser("iexplore").Page("*");

  // Totals for 1996
  var cell = page.contentDocument.all.item(73);// Obtains the page element
                                    // 73 is the index which we obtain through the Object Spy window
  var row = GetRow(cell); // Obtains the table row to which the element belongs
  var s = ProcessData(row);// Retrieves values from the row's cells
  Log.Message(s); // Posts data to the log

  // Totals for 1997
  cell = page.contentDocument.all.item(151);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);

  // Totals for 1998
  cell = page.contentDocument.all.item(208);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);
}

function ProcessData(ARow)
{
  var cell, s;

  // Obtains the value of the first column
  cell = ARow.cells.item(1);
  s = cell.innerText;

  // Obtains the value of the second column
  cell = ARow.cells.item(2);
  s = s + " Orders shipped: " + cell.innerText;

  // Obtains the value of the third column
  cell = ARow.cells.item(3);
  s = s + " Sales: " + cell.innerText;

  return s;
}

function GetRow(AObj)
{
  if (AObj != null)
  {
    if (AObj.tagName == "TD")
      return AObj.parentElement;
    else
      return GetRow(AObj.parentElement);
  }
  else
    return null;
}

Python

def ParseHTML():

  # Obtains the page object
  Browsers.Item[btIExplorer].Run("file:///C:\\Summary of Sales by Year.html");
  page = Sys.Browser("iexplore").Page("*");

  # Totals for 1996
  cell = page.contentDocument.all.item(73); # Obtains the page element
                                            # 73 is the index which we obtain through the Object Spy window
  row = GetRow(cell); # Obtains the table row to which the element belongs
  s = ProcessData(row); # Retrieves values from the row's cells
  Log.Message(s); # Posts data to the log

  # Totals for 1997
  cell = page.contentDocument.all.item(151);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);

  # Totals for 1998
  cell = page.contentDocument.all.item(208);
  row = GetRow(cell);
  s = ProcessData(row);
  Log.Message(s);

def ProcessData(ARow):
  # Obtains the value of the first column 
  cell = ARow.cells.item(1);
  s = cell.innerText;

  # Obtains the value of the second column
  cell = ARow.cells.item(2);
  s = s + " Orders shipped: " + cell.innerText;

  # Obtains the value of the third column 
  cell = ARow.cells.item(3);
  s = s + " Sales: " + cell.innerText; 

  return s;

def GetRow(AObj):
  if (AObj != None):
    if (AObj.tagName == "TD"):
      return AObj.parentElement;
    else:
      return GetRow(AObj.parentElement);
  else:
    return None;

VBScript

Sub ParseHTML
  Dim page, cell, row, s

  ' Obtains the page object
  Browsers.Item(btIExplorer).Run("file:///C:\Summary of Sales by Year.html")
  Set page = Sys.Browser("iexplore").Page("*")

  ' Totals for 1996
  Set cell = page.contentDocument.all.item(73) ' Obtains the page element
                                        ' 73 is the index which we obtain through the Object Spy window
  Set row = GetRow(cell) ' Obtains the table row to which the element belongs
  s = ProcessData(row) ' Retrieves values from the row's cells
  Log.Message s ' Posts data to the log

  ' Totals for 1997
  Set cell = page.contentDocument.all.item(151)
  Set row = GetRow(cell)
  s = ProcessData(row)
  Log.Message s

  ' Totals for 1998
  Set cell = page.contentDocument.all.item(208)
  Set row = GetRow(cell)
  s = ProcessData(row)
  Log.Message s
End Sub

Function ProcessData(ARow)
  Dim cell, s

  ' Obtains the value of the first column
  Set cell = ARow.cells.item(1)
  s = cell.innerText
  
  ' Obtains the value of the second column
  Set cell = ARow.cells.item(2)
  s = s + " Orders shipped: " + cell.innerText
  
  ' Obtains the value of the third column
  Set cell = ARow.cells.item(3)
  s = s + " Sales: " + cell.innerText

  ProcessData = s
End Function

Function GetRow(AObj)
  If Not (AObj Is Nothing) Then
    If AObj.tagName = "TD" Then
      Set GetRow = AObj.parentElement
    Else
      Set GetRow = GetRow(AObj.parentElement)
    End If 
  Else
    Set GetRow = Nothing
  End If 
End Function

DelphiScript

// Forward declarations
function ProcessData(ARow); forward;
function GetRow(AObj); forward;

procedure ParseHTML;
var
  page, cell, row, s : OleVariant;
begin
  // Obtains the page object
  Browsers.Item(btIExplorer).Run('file:///C:\Summary of Sales by Year.html');
  page := Sys.Browser('iexplore').Page('*');

  // Totals for 1996
  cell := page.contentDocument.all.item(73);// Obtains the page element
                                     // 73 is the index which we obtain through the Object Spy window
  row := GetRow(cell);  // Obtains the table row to which the element belongs
  s := ProcessData(row);// Retrieves values from the row's cells
  Log.Message(s);       // Posts data to the log

  // Totals for 1997
  cell := page.contentDocument.all.item(151);
  row := GetRow(cell);
  s := ProcessData(row);
  Log.Message(s);

  // Totals for 1998
  cell := page.contentDocument.all.item(208);
  row := GetRow(cell);
  s := ProcessData(row);
  Log.Message(s);
end;

function ProcessData(ARow);
var
  cell, s : OleVariant;
begin
  // Obtains the value of the first column
  cell := ARow.cells.item(1);
  s := cell.innerText;

  // Obtains the value of the second column
  cell := ARow.cells.item(2);
  s := s + ' Orders shipped: ' + cell.innerText;

  // Obtains the value of the third column
  cell := ARow.cells.item(3);
  s := s + ' Sales: ' + cell.innerText;

  Result := s;
end;

function GetRow(AObj);
begin
  if AObj <> nil then
  begin
    if AObj.tagName = 'TD' then
      Result := AObj.parentElement
    else
      Result := GetRow(AObj.parentElement);
  end
  else
    Result := nil;
end;

C++Script, C#Script

function ParseHTML()
{

  // Obtains the page object
  Browsers["Item"](btIExplorer)["Run"]("file:///C:\\Summary of Sales by Year.html");
  var page = Sys["Browser"]("iexplore")["Page"]("*");

  // Totals for 1996
  var cell = page["contentDocument"]["all"]["item"](73);// Obtains the page element
                                             // 73 is the index which we obtain through the Object Spy window
  var row = GetRow(cell); // Obtains the table row to which the element belongs
  var s = ProcessData(row);// Retrieves values from the row's cells
  Log["Message"](s); // Posts data to the log

  // Totals for 1997
  cell = page["contentDocument"]["all"]["item"](151);
  row = GetRow(cell);
  s = ProcessData(row);
  Log["Message"](s);

  // Totals for 1998
  cell = page["contentDocument"]["all"]["item"](208);
  row = GetRow(cell);
  s = ProcessData(row);
  Log["Message"](s);
}

function ProcessData(ARow)
{
  var cell, s;

  // Obtains the value of the first column
  cell = ARow["cells"]["item"](1);
  s = cell["innerText"];

  // Obtains the value of the second column
  cell = ARow["cells"]["item"](2);
  s = s + " Orders shipped: " + cell["innerText"];

  // Obtains the value of the third column
  cell = ARow["cells"]["item"](3);
  s = s + " Sales: " + cell["innerText"];

  return s;
}

function GetRow(AObj)
{
  if (AObj != null)
  {
    if (AObj["tagName"] == "TD")
      return AObj["parentElement"];
    else
      return GetRow(AObj["parentElement"]);
  }
  else
    return null;
}

The main routine is ParseHTML. At the beginning, this routine obtains the Page object that corresponds to the tested web page. In the given example, the page belongs to the explorer process, not to iexplore (see About Web Object Identification and Object Models for more information). Then, the routine obtains the web page element by its index, retrieves data and posts them to the log:

JavaScript, JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

cell = Page.document.all.item(73);
row = GetRow(cell);
s = ProcessData(row);
Log.Message(s);
// ...

Python

cell = Page.document.all.item(73);
row = GetRow(cell);
s = ProcessData(row);
Log.Message(s);
# ...

VBScript

Set cell = Page.document.all.item(73)
Set row = GetRow(cell)
s = ProcessData(row)
Log.Message s
' ...

DelphiScript

cell := Page.document.all.item(73);
row := GetRow(cell);
s := ProcessData(row);
Log.Message(s);
// ...

C++Script, C#Script

cell = Page["document"]["all"]["item"](73);
row = GetRow(cell);
s = ProcessData(row);
Log["Message"](s);
// ...

The cell variable refers to the web page element that contains the summary text. We pass this variable to the GetRow function that returns the TR element for the desired cell. Why do we use a special routine and do not obtain the TR by using the cell.parentElement property? The text may belong to a child element (B, I, FONT) rather than TD. In our example, the text belongs to the FONT element. The whole hierarchy looks like --

<TR>

<TD>

<B>

<I>

<FONT>text</FONT>

...

The special GetRow function that we wrote walks up the hierarchy and returns the TR element containing summary values.

After we obtain the TR element, we pass it to the ProcessData function for processing. This function uses the call.items method to obtain the TD elements and then uses their innerText property to get values:

JavaScript, JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript, JScript

// Obtains the value of the first column
cell = ARow.cells.item(1);
s = cell.innerText;
// ...

Python

# Obtains the value of the first column
cell = ARow.cells.item(1);
s = cell.innerText;
# ...

VBScript

' Obtains the value of the first column
Set cell = ARow.cells.item(1)
s = cell.innerText
' ...

DelphiScript

// Obtains the value of the first column
cell := ARow.cells.item(1);
s := cell.innerText;
// ...

C++Script, C#Script

// Obtains the value of the first column
cell = ARow["cells"]["item"](1);
s = cell["innerText"];
// ...

After you execute the script, you will see the report data in the test log.

See Also

Testing Microsoft Access Applications
Testing Web Applications
About Object Spy
Optical Character Recognition

Highlight search results