Using VBA and Databases to create HTML

Here’s some code I used to generate HTML for a web configuration file. It takes a database (the current open one) then looks to a query called QueryTargetInformation and places the fields – PlaceName / EastingMn / NorthingMn / EastingMx / NorthingMx in a HTML Structure and creates a file called CodeGeneratedHTML.txt place it on the C drive.

I put around 1,000 repeated links in HTML configuration file using this.

This was for a web mapping application – the eastings and northings were obtained from Ordnance Survey Open Source shape files from Ordnance Survey and then QGIS to get the eastings and northings of a variety of locations. These were transferred into the relevant columns of a database and this code triggered from the onclick event of a form command.

Private Sub Command_Click()
On Error GoTo Err_Command_Click
 
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryTargetInformation")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\CodeGeneratedHTML.txt", True)
Do Until rst.EOF = True
TextFile.WriteLine ("<bookmark name=" & Chr$(34) & rst!PlaceName & Chr$(34) & ">")
TextFile.WriteLine ("   <min>")
TextFile.WriteLine ("       <x>" & rst!EastingMn & "</x>")
TextFile.WriteLine ("       <y>" & rst!NorthingMn & "</y>")
TextFile.WriteLine ("   </min>")
TextFile.WriteLine ("   <max>")
TextFile.WriteLine ("       <x>" & rst!EastingMx & "</x>")
TextFile.WriteLine ("       <y>" & rst!NorthingMx & "</y>")
TextFile.WriteLine ("   </max>")
TextFile.WriteLine ("</bookmark>")
rst.MoveNext
Loop
TextFile.Close
 
MsgBox "Created CodeGeneratedHTML File in C drive"
 
Exit_Command_Click:
    Exit Sub
 
Err_Command_Click:
    MsgBox Err.Description
    Resume Exit_Command_Click
 
End Sub