Consider the situation where you have a business that sells hundreds of products and the inventory is recorded in a database table (recordset). A report of the inventory normally exceeds 50 pages and is produced regularly using ActiveReports. The products in this inventory report are listed in Groups and Subgroups with a table of contents (TOC) in the data report window (AR Viewer) that links to the Groups and Subgroups. The report and its TOC can be exported to .pdf file format, which can be uploaded to a Web site for customers to download, but it requires customers to have a .pdf viewer software installed (Adobe or PDF Converter).
In order to avoid this, the preference is to have the inventory report exported to HTML file format (*.htm). A .htm file fits better with the requirements of the Web site, with hyperlinks from the products listed in the report pointing to other Web pages giving detailed product information. However, a problem with exporting an ActiveReport to .htm file format is that the TOC is lost. This can make it difficult to locate an inventory Group or Subgroup and the products listed within, especially if the .htm file is large.
The following demonstrates a method in which you can create a TOC for an .htm file with hyperlinks pointing to Groups and Subgroups within the .htm file. The entire process occurs via automation at the time the ActiveReport is exported to .htm file format, and requires no further editing of the underlying HTML code. It is assumed you have a project on hand where the steps below can be applied.
- Create Two Collections.
In a VB project add 2 collection object variables to a code module (.bas module):
Public colHTMStrings1 As Collection
Public colHTMStrings2 As Collection
Note: While a dynamic array could be used instead of the above collections, the use of collections is simply the author’s choice and avoids the need to continually Redim an array.
- Imbed a TOC for the .htm file in the ActiveReport.
Note: A TOC in a .htm file has to be included in the main window and form part of the report in the same way as a TOC in a book occurs at the front of a book.
Assume an inventory report with 2 group header sections. GroupHeader1 groups the Group field data, and GroupHeader2 groups the Subgroup field data. Groups are listed alphabetically. Sub groups within Groups are also listed alphabetically, as are Products listed within Subgroups. A TOC occurring near the top of an .htm file contains only Groups and Subgroups field data, with each Group and Subgroup listed once. Subgroups text is indented from the Groups text, and all lines of text in the TOC act as hyperlinks to bookmarks elsewhere in the .htm file.
A TOC as outlined above is easiest achieved by using a subreport linked via a sub report control in the main report. In this case, the subreport is called rptProdIndex and includes a GroupHeader section. The GroupHeader section links to the Groups field, and the Detail section is used to show the Subgroup items.
Note: In the following code, clsADODB is a wrapper class used by the author to manage ADO recordsets, while cADO is an instance of this wrapper class.
Option Explicit
Private cADO As clsADODB
Public Database As String
Public DatabasePath As String
Private UseHyperlinks As Boolean
Public Filter As String
Private Sub ActiveReport_ReportStart()
On Error GoTo vbError
UseHyperlinks = frmInventory.bUseHyperlinks
If UseHyperlinks Then
txtMainGroup.ForeColor = &HFF0000
txtSubGroup.ForeColor = &HFF0000
End If
Set cADO = New clsADODB
With cADO
.Database = Database
.DatabasePath = DatabasePath
If Filter <> "" Then
.Recordsource = "SELECT DISTINCTROW MainGroup, SubGroup From Inventory WHERE " & Filter & " Group By MainGroup, SubGroup"
Else
.Recordsource = "SELECT DISTINCTROW MainGroup, SubGroup From Inventory " & _
"Group By MainGroup, SubGroup"
End If
.OpenRecordset adUseClient, adOpenDynamic, adLockOptimistic
Set DC1.Recordset = .Recordset
End With
txtMainGroup.DataField = "MainGroup"
txtSubGroup.DataField = "SubGroup"
GroupHeader1.DataField = "MainGroup"
Exit Sub
vbError:
cADO.Error
Resume Next
End Sub
Private Sub ActiveReport_ReportEnd()
On Error GoTo vbError
If Not cADO.Connection Is Nothing Then
cADO.Connection.Close
Set cADO.Connection = Nothing
End If
cADO.CloseADODB
Set cADO = Nothing
Exit Sub
vbError:
cADO.Error
End Sub
Private Sub Detail_Format()
On Error GoTo vbError
If UseHyperlinks Then txtSubGroup.Hyperlink = "#" & txtMainGroup.Text & txtSubGroup.Text
Exit Sub
vbError:
cADO.Error
End Sub
Private Sub GroupHeader1_Format()
On Error GoTo vbError
If UseHyperlinks Then txtMainGroup.Hyperlink = "#" & txtMainGroup.Text
Exit Sub
vbError:
cADO.Error
End Sub
In the above code, it is important to note the SQL syntax used to return the required Group and Subgroup headings from the recordset and how the query results are displayed in the subreport. Additionally, you should understand how the txtMainGroup and txtSubGroup hyperlinks are created:
- The txtMainGroup hyperlink is created by using the Group data from the recordset, preceeded with the # character.
- The txtSubGroup hyperlink is created by appending the Subgroup data to the Group data from the recordset and preceeding with the # character.
By using the above technique, every hyperlink in the TOC will be unique, automatically correlate with Group and Subgroup field data, and point to a target in the parent report.
- Preliminary Setting up the Bookmarks (Targets) in the Main Report (Parent report)
In the parent report, the GrouperHeader1 section shows the Groups and the GroupHeader2 section shows the Subgroups. Each of these sections contains a textbox named txtGroup and txtSubGroup, respectively. (The Detail section shows the Product Names via txtProductName.)
Initially the Groups and Subgroups textboxes have to be set up with normal hyperlinks. The hyperlink HTML code (HTML syntax) is added to the colHTMStrings1 collection. The required bookmark or target HTML code (also HTML syntax) is added to the colHTMStrings2 collection.
As with the TOC hyperlinks, the target hyperlinks use the Group and Subgroup field data but do not include the # character.
Private Sub ActiveReport_ReportStart()
Set colHTMStrings1 = New Collection
Set colHTMStrings2 = New Collection
End Sub
Private Sub GroupHeader1_Format()
TOC.Add txtGroup.Text
txtGroup.Hyperlink = txtGroup.Text
colHTMStrings1.Add "<A HREF=" & Chr$(34) & txtGroup.Text & Chr$(34) & ">" 'Chr$(34) represents the double quote character
colHTMStrings2.Add "<A NAME=" & Chr$(34) & txtGroup.Text & Chr$(34) & ">" 'NAME represents the target in HTML
End Sub
Private Sub GroupHeader2_Format()
TOC.Add txtGroup.Text & "\" & txtSubGroup.Text
txtGroup.Hyperlink = txtGroup.Text & txtSubGroup.Text
colHTMStrings1.Add "<A HREF=" & Chr$(34) & txtGroup.Text & txtSubGroup.Text & Chr$(34) & ">"
colHTMStrings2.Add "<A NAME=" & Chr$(34) & txtGroup.Text & txtSubGroup.Text & Chr$(34) & ">"
End Sub
- Form Module Code (Cleaning the HTML Code).
Assume in a Form Module that you are viewing database inventory records, and these records may be filtered to show only a subset of records you want to send to the data report and on to an .htm file for Web deployment. The following code demonstrates how you to create the .htm file with the click of a command button.
Private Sub cmdCreateMainHTML_Click()
Dim i As Integer
Dim rpt As Object
Dim str As String
Dim strHTM As String
'Product, Retail, Grouped, Corporate format
Set rpt = New rptPrRtGroCorporate
'create the report in memory
Load rpt
'using the wrapper class to manage the ADO recordset (author's choice)
'other users of AR will have other means
With rpt
.Database = cADO.Database
.DatabasePath = cADO.DatabasePath
.Recordsource = cADO.Recordsource
.Filter = cADO.FilterString
End With
rpt.Run False
If Not rpt Is Nothing Then
'export the report to html
Dim html As ActiveReportsHTMLExport.HTMLExport
Set html = New ActiveReportsHTMLExport.HTMLExport
html.FileNamePrefix = txtFileName.Text
html.HTMLOutputPath = lblFolder.Caption & "\"
html.Title = txtFileName.Text & ".htm"
If rpt.Pages.Count > 0 Then
html.Export rpt.Pages 'creates the initial .htm file
'for grouped reports change hyperlinks to targets
'open file for input
Open lblFolder.Caption & "\" & txtFileName.Text & ".htm" For Input As #1
str = ""
Do While Not eof(1) 'Loop until end of file.
Input #1, strHTM 'Read data
str = str & strHTM & vbCrLf
Loop
Close #1 'Close file.
'the HTML text of the .htm file is now in a string variable str
'modify the str variable so that hyperlinks are converted to targets
For i = 1 To colHTMStrings1.Count
str = Replace(str, colHTMStrings1(i), colHTMStrings2(i))
Next
'open for output
Open lblFolder.Caption & "\" & txtFileName.Text & ".htm" For Output As #1
Print #1, str 'Print str to file
Close #1 'Close file.
Set colHTMStrings1 = Nothing
Set colHTMStrings2 = Nothing
End If
MsgBox "HTML file " & txtFileName.Text & " .htm has been successfully created.", _
vbInformation, "Done"
End If
Set rpt = Nothing
Set html = Nothing
End Sub
The actual flow of the process is simple. What is happening with the sub procedure above is the data report is being exported to a .htm file. The text of this .htm file is then read into a string variable (str), which is modified using the Replace function to replace selected hyperlink substring text with target substring text. The modified str string is then read back into the .htm file.
It follows that when viewing a group or subgroup in the .htm data report, you want to be able to click a hyperlink to return back to the TOC at the top of the file as an alternative to scrolling the Web browser.
To create this type of link:
- Place a second textbox in the GroupHeader2 section, name it txtContentsMenu, and set its forecolor and text properties to &H00FFFFFF& and "Contents Menu,"
respectively.
- In the properties window for txtContentsMenu, set the hyperlink property to #Page1.
This will create a hyperlink back to page 1, which is where the TOC is located.