Code to produce HTML from Query

  • Thread starter Thread starter Paul Proefrock
  • Start date Start date
P

Paul Proefrock

I have a query that produces membership information, used for posting on the
internet. If we run the query to export as HTML, the output is loaded with
HTML tags, that really aren't necessary. I can cut the file size by 75% by
removing these tags.

Removing the tags is presently being done manually. Is there a way to
control the tags that Access puts into an exported HTML file or perhaps code
that will do a similar thing.

Thanks

Paul
(I posed this question about two years ago and received a comment but I
can't find the reply and Google has also been unsuccessful - can't find the
correct combination of search words)
 
Here is a function that will accept a query or table name and return a
string of HTML. You can specify whether you want headings or not. I suppose
you could pass in a file name if you wanted the HTML to go directly into a
file.

Function TableQueryToHTML(strQueryName As String, _
Optional booHeadings As Boolean = -1) As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strHTML As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [" & strQueryName & "]")
strHTML = "<Table>" & Chr(13) & Chr(10)
If booHeadings = True Then
strHTML = strHTML & " <tr>" & Chr(13) & Chr(10)
For Each fld In rs.Fields
strHTML = strHTML & " <th>" & _
fld.Name & "</th>" & Chr(13) & Chr(10)
Next
strHTML = strHTML & " </tr>" & Chr(13) & Chr(10)
End If

With rs
Do Until .EOF
strHTML = strHTML & " <tr>" & Chr(13) & Chr(10)
For Each fld In .Fields
strHTML = strHTML & " <td>" & _
fld.Value & "</td>" & Chr(13) & Chr(10)
Next
strHTML = strHTML & " </tr>" & Chr(13) & Chr(10)
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
strHTML = strHTML & "</Table>"
TableQueryToHTML = strHTML
End Function
 
Back
Top