Access 2K & single page HTML report: possible solution?

[Guest]

I've cobbled together a possible solution to creating a single page HTML
report based on an existing report. Ain't elegant, but it works. Comments &
suggestions are welcome.

To start, create a table with fields for report name, HTML code before data,
HTML detail code, SQL command to select data. I generated the HTML code from
exporting the report as HTML, then replacing data with the string "$data".
SQL commands copied from query on which report is based. My table is called
tblHTMLrpt.

Then the code:

***BEGIN CODE***
Sub HTMLReport(strReportName As String)

Dim rstHTML As Recordset, rstRPT As Recordset
Dim strSQLrpt As String, strSQLdata As String
Dim strFileName As String
Dim strHDR As String
Dim strDATA As String
Dim strRepData As String
Dim intFileNbr As Integer
Dim intRecCount As Integer
Dim intFldCount As Integer, i As Integer

strSQLrpt = "SELECT * FROM tblHTMLRpt WHERE frptname ='" & strReportName & "'"
Set rstHTML = CurrentDb.OpenRecordset(strSQLrpt)
If rstHTML.RecordCount = 0 Then
MsgBox "Ain't nobody home"
Exit Sub
End If

intFileNbr = FreeFile(0)
strFileName = strReportName & ".HTML"

strSQLdata = rstHTML!fRptSQL
Set rstRPT = CurrentDb.OpenRecordset(strSQLdata)

If rstRPT.RecordCount = 0 Then
MsgBox "No records, so no report."
Exit Sub
End If
rstRPT.MoveLast
intRecCount = rstRPT.RecordCount
rstRPT.MoveFirst
intFldCount = rstRPT.Fields.Count

Open strFileName For Output As intFileNbr
strHDR = rstHTML!fHDR
Print #intFileNbr, strHDR

Do While Not rstRPT.EOF
strDATA = rstHTML!fdata
For i = 0 To intFldCount - 1
strRepData = Nz(rstRPT.Fields.Item(i))
If InStr(rstRPT.Fields.Item(i).Name, "Website") > 0 Then
strRepData = "<A HREF=""http://" & strRepData & """>" &
strRepData & "</A>"
End If
strDATA = Replace(strDATA, "$data", strRepData, , 1)
Next i
Print #intFileNbr, strDATA
rstRPT.MoveNext
Loop

Close intFileNbr
rstHTML.Close
rstRPT.Close
Set rstHTML = Nothing
Set rstRPT = Nothing

End Sub
***END CODE***

George

 

[Guest]

OOPS. For completeness, the HTML file should end with closing BODY & HTML
tags. So...

Print #intFileNbr, strDATA
rstRPT.MoveNext
Loop

'add following line for completeness
Print #intFileNbr, "</body></html>"

Close intFileNbr

What I like about this method is that it allows one to use any HTML code,
not just what Access exports.