Objects

XLReport Object

Represents the XLReportCom. XLReport is the main class for report generation using XLReportCom.

Using the XLReport Object

The following example creates an XLReport object in another application and then generates a report using an XRF file.

Dim xlrpt As XLReport
Set xlrpt = New XLReport
xlrpt.ExcelReport xlApp, "customer_list.xrf"

Methods

FixTableReport Method

Generates a fixed table report based on a template. In a fixed table report, the number of rows and columns is fixed. XLReportCom gets data from a recordset object, and directly fills data into the cells of a worksheet.

Syntax

object.FixTableReport(Recordset, Worksheet, CellList, Range, Copy, FillOrder, ImageList, PageBreak)

object  Required. The object is the XLReport object.

Recordset  Required. An object variable that represents the ADODB.Recordset object to provides data.

Worksheet  Required. An object variable that represents the Excel.Worksheet object to be filled data.

CellList  Required. A string that represents the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the CellList should correspond to the data source fields in the recordset. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ......

Range  Optional. A string that indicates the range in the worksheet to be used for the records. XLReportCom will skip or repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the records.

Copy  Optional. An integer that indicates whether XLReportCom will copy the range for each record. If the value is zero, XLReportCom will skip the rows/columns of the range for each record. Otherwise it will copy the original range to the range where data will be filled for each record.

FillOrder  Optional. An integer that indicates the order in which XLReportCom fills data. If the value is zero, fills data by rows. Otherwise fills data by columns. Default is 0.

ImageList  Optional. A string that indicates which data source fields are the picture files. The ImageList is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the worksheet.

PageBreak  Optional. A string that indicates the page breaks. The unit of page length is r that means record. For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records. Default is "" that means no page break.

Example

This example uses FixTableReport method to make the report "Top 5 Employees for Sales".

1. Create the template in Microsoft Excel.

2. Write the code in your application.

    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    con.ConnectionString = "Data Source=Report Sample"
    con.Open
    strSQL = "SELECT TOP 5 e.FirstName + ' ' + e.LastName, SUM(d.Quantity)
	    , Sum(d.UnitPrice * d.Quantity * (1-d.Discount)) AS SalesAmount 
	    FROM Orders o, OrderDetails d, Products p, Employees e 
	    WHERE o.OrderID = d.OrderID 
	    AND d.ProductID = p.ProductID 
	    AND o.EmployeeID = e.EmployeeID 
	    AND YEAR(o.OrderDate) = 1996 
	    AND MONTH(o.OrderDate) = 04 
	    GROUP BY e.FirstName, e.LastName 
	    ORDER BY 3 DESC"
    rec.Open strSQL, con
    xlrpt.FixTableReport Recordset:=rec, Worksheet:=xlWorksheet, CellList:="B7"
    rec.Close

3. Generate the report.

VarTableReport Method

Generates a variable table report based on a template. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. XLReportCom gets data from a recordset object, inserts some blank rows/columns or copy a range for each record, then fills data into the cells of a worksheet.

Syntax

object.VarTableReport(Recordset, Worksheet, CellList, Range, Copy, Reserve, FillOrder, ImageList, PageBreak, NoData)

object  Required. The object is the XLReport object.

Recordset  Required. An object variable that represents the ADODB.Recordset object to provides data.

Worksheet  Required. An object variable that represents the Excel.Worksheet object to be filled data.

CellList  Required. A string that represents the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the CellList should correspond to the data source fields in the recordset. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ......

Range  Optional. A string that indicates the range in the worksheet to be used for the records. XLReportCom will repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the records.

Copy  Optional. An integer that indicates whether XLReportCom will copy the range for each record. If the value is zero, XLReportCom will insert the blank rows/columns of the range for each record. Otherwise it will copy the source range and insert the copied range for each record.

Reserve  Optional. An integer that indicates the number of records for which you reserved some rows/columns in the report template for the report. Possible values are 1 or 2. One means you reserve some rows/columns for one record, and two means some rows/columns for two records. Default is 1.

FillOrder  Optional. An integer that indicates the order in which XLReportCom fills data. If the value is zero, fills data by rows. Otherwise fills data by columns. Default is 0.

ImageList  Optional. A string that indicates which data source fields are the picture files. The ImageList is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the worksheet.

PageBreak  Optional. A string that indicates the page breaks. The unit of page length is r that means record. For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records. Default is "" that means no page break.

NoData  Optional. An integer that represents an option when no data are returned from data source. If the value is not zero, XLReportCom will delete the range when no data are returned. Default is 0.

Example

This example uses VarTableReport method to make the report "Mail Label".

1. Create the template in Microsoft Excel.

2. Write the code in your application.

    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    con.ConnectionString = "Data Source=Report Sample"
    con.Open
    strSQL = "SELECT CompanyName, Address, CityName & ', ' & CountryName, PostalCode 
	    FROM Customers, Cities, Countries 
	    WHERE Customers.CityCode = Cities.CityCode 
	    AND Customers.CountryCode = Cities.CountryCode 
	    AND Customers.CountryCode = Countries.CountryCode
	    ORDER BY CompanyName"
    rec.Open strSQL, con
    xlrpt.VarTableReport Recordset:=rec, Worksheet:=xlWorksheet, _
        CellList:=" B7,B8,B9,B10", Range:="1:11", Copy:=1, PageBreak:="4r"
    rec.Close

3. Generate the report.

GroupTableReport Method

Generates a variable table report based on a template, and groups data in the report. In a variable table report, the number of rows or columns in the table is unfixed, and it is variable as the number of the result records. XLReportCom gets data from a recordset object, copy the group range for each group, and copy the detail range for each record.

Syntax

object.GroupTableReport(Recordset, Worksheet, CellList, Range, FillOrder, ImageList, PageBreak, NoData, Group1, GroupRange1, ... Group10, GroupRange10)

object  Required. The object is the XLReport object.

Recordset  Required. An object variable that represents the ADODB.Recordset object to provides data.

Worksheet  Required. An object variable that represents the Excel.Worksheet object to be filled data.

CellList  Required. A string that represents the list of cells separated by the "," character. For example, "A2,B2,B3,D2,D3". The cells in the CellList should correspond to the data source fields in the recordset. The value of the first field is put into the first cell, and the value of the second field is put into the second cell ......

Range  Optional. A string that indicates the range in the worksheet to be used for the details. XLReportCom will repeat the range for each record. You can reference a range of cells like "2:4" or "B2:D5". The default range is the area that includes all cells for the details.

FillOrder  Optional. An integer that indicates the order in which XLReportCom fills data. If the value is zero, fills data by rows. Otherwise fills data by columns. Default is 0.

ImageList  Optional. A string that indicates which data source fields are the picture files. The ImageList is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In data source, you stored the path and file name of the picture, not the picture. The file path can be a relative path, an absolute path or a URL. If it is a relative path, the base path is the path of the worksheet.

PageBreak  Optional. A string that indicates the page breaks. The unit of page length is r or g. "r" means record, "g1" means group one, "g2" means group two...... For example, "6r" or "6" means that XLReportCom will insert a page break per 6 records, "1g1" or "1g" means a page break per group one, and "1g1,6r" means a page break per group one or 6 records. Default is "" that means no page break.

NoData  Optional. An integer that represents an option when no data are returned from data source. If the value is not zero, XLReportCom will delete the range when no data are returned. Default is 0.

Group1...Group10  Optional. A string that indicates the group that is the list of data source fields separated by the "," character. You can identify a field using the name of field or the index number of field, but not simultaneously. In one report, there may be up to 10 groups. Notes: the order of groups should be in accordance with the order of ORDER BY clause in the SQL statement.

GroupRange1...GroupRange10  Optional. A string that indicates the range of the group in the worksheet. XLReportCom will repeat the range for each group. The range of the group should contain the range of the details and the area that includes all cells for this group. You reference a group range like "2:4" or "B2:D5". For example, there are two groups, the range of the group one contains all cells for the group one and the range of the group two, and the range of the group two contains all cells for the group two and the range of the details. The default range is the area that includes all cells for this group and the range or the group range for the lower level group.

Example

This example uses GroupTableReport method to make the report "Customer Profile".

1. Create the template in Microsoft Excel.

2. Write the code in your application.

    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    con.ConnectionString = "Data Source=Report Sample"
    con.Open
    strSQL = "SELECT LEFT(CompanyName,1), CompanyName, ContactName
	    , 'Phone: ' & Phone, 'Fax: ' & Fax, Address
	    , CityName & ', ' & CountryName, PostalCode 
	    FROM Customers, Cities, Countries 
	    WHERE Customers.CityCode = Cities.CityCode 
	    AND Customers.CountryCode = Cities.CountryCode 
	    AND Customers.CountryCode = Countries.CountryCode 
	    ORDER BY CompanyName"
    rec.Open strSQL, con
    xlrpt.VarTableReport Recordset:=rec, Worksheet:=xlWorksheet, _
        CellList:="A6,B7,C7,D7,D8,E7,E8,E9", Range:=" 6:9", _
        Group1:= "1", PageBreak:="6r"
    rec.Close

3. Generate the report.

SetExcelName Method

Gets data from a recordset object, and assigns the values to the names defined in the Excel workbook. XLReportCom will just fetch the first record, no matter how many records are returned from data source.

Syntax

object.SetExcelName(Recordset, Workbook, NameList)

object  Required. The object is the XLReport object.

Recordset  Required. An object variable that represents the ADODB.Recordset object to provides data.

Worksheet  Required. An object variable that represents the Excel.Worksheet object to be filled data.

NameList  Required. A string that represents the names you want assign values to. The NameList is the list of names separated by the "," character. For example, "BeginDate, EndDate" means two names: BeginDate and EndDate that should be defined in the report template. The names in the list should correspond to the fields in the SQL statement. The value of the first field is put into the first name, and the value of the second field is put into the second name ...

Example

This example uses SetExcelName method to assign the values of fields to names.

1. Define the names BeginDate and EndDate in the report template in Microsoft Excel.

2. Write the code in your application.

    Set con = New ADODB.Connection
    Set rec = New ADODB.Recordset
    con.ConnectionString = "Data Source=Report Sample"
    con.Open
    strSQL = " SELECT min_date, max_date FROM tmp0"
    rec.Open strSQL, con
    xlrpt.SetExcelName Recordset:=rec, Worksheet:=xlWorksheet, _
    NameList:="BeginDate,EndDate"
    rec.Close

ExcelReport Method

Generates the reports based on the templates and a XRF file. The XRF file tells XLReportCom how to get data from data sources and how to put data into the reports.

Syntax

object.ExcelReport(Application, XrfFile, Param1 ... Param10)

object  Required. The object is the XLReport object.

Application  Required. An object variable that represents the Excel.Application object.

XrfFile  Required. A string that represents the XRF file. You can include a full path.

Param1 ... Param10  Optional. A string that represents the paramters. These parameters have been defined in the XRF file.

Example

This example uses ExcelReport method to make the report "Customer List".

1. Create the template customer_list.xls using Microsoft Excel.

2. Create the XRF file customer_list.xrf using a text editor.

3. Write the code in your application.

    Set xlApp = New Excel.Application
    Set xlrpt = New XLReport
    Call xlrpt.ExcelReport(xlApp, "customer_list.xrf")

Events

BeforeConnect Event

Occurs before a connection starts.

Syntax

Private Sub object_BeforeConnect(UserID As String, Password As String, DataSource As String, Connection As ADODB.Connection)

object  The object is the XLReport object.

UserID  A string that represents a user name for the connection.

Password  A string that represents a password for the connection.

DataSource  A string that represents a data source name for the connection.

Connection  The ADODB.Connection object.

Example

Private Sub mxlrpt_BeforeConnect(UserID As String, Password As String, _
    DataSource As String, Connection As ADODB.Connection)
    Connection.ConnectionTimeout = 15
    Connection.CursorLocation = adUseClient
End Sub

TemplateOpen Event

Occurs when a template workbook is opened.

Syntax

Private Sub object_TemplateOpen(ByVal Workbook As Excel.Workbook)

object  The object is the XLReport object.

Workbook  An object variable that represents the Excel.Workbook object to be opened.

Example

Private Sub mxlrpt_TemplateOpen(ByVal Workbook As Excel.Workbook)
    Dim i As Integer
    
    With Workbook
        If .Application.Visible Then
            For i = .Worksheets.Count To 1 Step -1
                .Worksheets(i).DisplayPageBreaks = False
            Next i
        End If
    End With
End Sub

ReportComplete Event

Occurs when all report generating process is completed.

Syntax

Private Sub object_ReportComplete(ByVal Workbook As Excel.Workbook)

object  The object is the XLReport object.

Workbook  An object variable that represents the Excel.Workbook object.

Example

Private Sub mxlrpt_ReportComplete(ByVal Workbook As Excel.Workbook)
    ' Close the workbook and do not display the report when get errors
    If mintErrCount > 0 Then
        Workbook.Close
    End If
End Sub

FunctionBeforeExectue Event

Occurs before a function is executed.

Syntax

Private Sub object_FunctionBeforeExectue(ByVal FunctionNo As String, ByVal FunctionType As Integer, ByVal SQLNo As Long, ByVal SQLText As String)

object  The object is the XLReport object.

FunctionNo  A string that represents the label of the function.

FunctionTyp  An integer that represents the type of the function. 0 means ExecSQL function. 1 means Name function. 2 means Report function.

SQLNo  A long that represents the number of SQL statements.

SQLText  A string that contains the SQL statement.

Example

Private Sub mxlrpt_FunctionBeforeExectue(ByVal FunctionNo As String, _
    ByVal FunctionType As Integer, ByVal SQLNo As Long, _
    ByVal SQLText As String)
    frmWait.lblFunctionNo = FunctionNo
    frmWait.lblSQLCount = SQLNo
End Sub

FunctionAfterExectue Event

Occurs after a function is executed.

Syntax

Private Sub object_FunctionAfterExectue(ByVal FunctionNo As String, ByVal FunctionType As Integer, ByVal SQLNo As Long, ByVal ErrObj As ErrObject)

object  The object is the XLReport object.

FunctionNo  A string that represents the label of the function.

FunctionTyp  An integer that represents the type of the function. 0 means ExecSQL function. 1 means Name function. 2 means Report function.

SQLNo  A long that represents the number of SQL statements.

ErrObj  The Err object.

Example

Private Sub mxlrpt_FunctionAfterExectue(ByVal FunctionNo As String, _
    ByVal FunctionType As Integer, ByVal SQLNo As Long, _
    ByVal ErrObj As ErrObject)
    If ErrObj.Number <> 0 Then
        If FunctionType <> 0 Then           'Ignore errors of EXECSQL
            MsgBox ErrObj.Description, vbExclamation, App.ProductName
            mintErrCount = mintErrCount + 1
        End If
    End If
End Sub

FunctionProgress Event

Occurs periodically during a function processing.

Syntax

Private Sub object_FunctionProgress(ByVal Progress As Long, ByVal RecordCount As Long)

object  The object is the XLReport object.

Progress  A long that indicates the number of records that have currently been processed.

RecordCount  A long that indicates the total number of records.

Example

Private Sub mxlrpt_FunctionProgress(ByVal Progress As Long, _
    ByVal RecordCount As Long)
    frmWait.lblRecordCnt.Caption = Format(Progress, "#,##0") & _
        " / " & Format(RecordCount, "#,##0")
End Sub

Error Messages

The following table lists the trappable errors for the XLReport Object.