How to Read the Contents of a LIMS Report Control from Excel

Summary

The information in this article applies to LIMS 2.x and Excel 97.

When you export data from a LIMS report, only the data from the report’s underlying recordset is exported to the Excel template’s LIMSData worksheet. This article describes the steps necessary to retrieve and display data from a LIMS report control.

More Information

When you use the Export to MS Excel Template toolbar button to export LIMS report data to Excel, only the data from the report’s main underlying recordset is exported to the template’s LIMSData worksheet. In some cases, such as with user-defined reports (UDR), you may also want to use data from other report controls. For example, you may have header, footer, or report text expressions in your UDR that you want to use in your Excel report.

In order to display data from LIMS report controls, the template must retrieve this data directly from the LIMS report object. The VBA code below is an excerpt from an AfterTransferFromLIMS macro which retrieves the contents of the title, header, footer, and report text controls from any UDR.

Dim objAccess As Access.Application

' Get the contents of the UDR's title, header, footer, and
' report text controls from the LIMS report in Access.
' NOTE: To get data from Access you must set a reference
' to the Microsoft Access 8.0 Object Library using
' Tools | References in the VBA editor.
On Error Resume Next
Set objAccess = GetObject(, "Access.Application")
If Err = 0 Then     ' Access is running
    With Worksheets("UDR Controls")
        .Range("A2") = _
            objAccess.Screen.ActiveReport!txtReportTitle
        .Range("A3") = _
            objAccess.Screen.ActiveReport!txtHeader1Left
        .Range("B3") = _
            objAccess.Screen.ActiveReport!txtHeader1Right
        .Range("A4") = _
            objAccess.Screen.ActiveReport!txtHeader2Left
        .Range("B4") = _
            objAccess.Screen.ActiveReport!txtHeader2Right
        .Range("A5") = _
            objAccess.Screen.ActiveReport!txtReportText
        .Range("A6") = _
            objAccess.Screen.ActiveReport!txtFooter1Left
        .Range("B6") = _
            objAccess.Screen.ActiveReport!txtFooter1Right
        .Range("A7") = _
            objAccess.Screen.ActiveReport!txtFooter2Left
        .Range("B7") = _
            objAccess.Screen.ActiveReport!txtFooter2Right
    End With
End If

The code above copies the contents of the title, report text, header, and footer text box controls from the UDR to cells in a template worksheet. Note that the text box control names above are common to all UDR templates so this code can be used with any UDR.

For an example, export the data from any UDR to the UDR Controls Example.xlt template from which this VBA code was copied. This file should be located in your LIMS Example Files folder. If you don’t have a copy of this file, you can download a current version of the Excel examples from the file library.