Using the resource above as a template, I am having an issue I cannot identify.
I would appreciate any assistance by a quick audit of this code to determine why I am getting an error on Line 20:
Open strHTMLFile For Output As #1
Access 2007 ACCDB VBA Module
The purpose of this would be to cycle thru multiple records in a table and return an html file for each present.
VBA:
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Private Sub sCreateStaticHTMLFiles_Click()
- Dim Db As DAO.Database
- Dim rs As DAO.Recordset
- Dim strHTMLFile As String
- Dim s As String
- Set Db = CurrentDb
- Set rs = Db.OpenRecordset("SELECT * FROM XTab ORDER BY ID")
- If rs.EOF Then
- MsgBox "Empty Table"
- s = Trim(rs!ID)
- strHTMLFile = "E:\RMS\XTAB\" & Format(s, "000") & ".htm"
- End If
- s = Trim(rs!ID)
- Open strHTMLFile For Output As #1
- Print #1, "<html><head></head><body><TABLE BORDER=1>"
- Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"
- If StrComp(s, Trim(rs!ID), vbTextCompare) <> 0 Then 'New file
- If diff Then
- Print #1, "</TABLE></body></html>"
- Close #1
- Open strHTMLFile For Output As #1
- Print #1, "<html><head></head><body><TABLE BORDER=1>"
- Print #1, "<TR bgcolor=E0E0E0 align=center><TD>Specialty</TD><TD>Business Unit</TD><TD>Cost Center</TD><TD>LOC_NAME</TD><TD>Status</TD><TD>Billing ID</TD><TD>Client_Name</TD><TD>ops_level_1</TD><TD>ops_level_2</TD><TD>ops_level_3</TD><TD>ops_level_4</TD><TD>ops_primary</TD><TD>client_level_4</TD><TD>client_primary</TD><TD>Total Of Balance</TD><TD>121 - 150</TD><TD>150+</TD><TD>61 - 90</TD><TD>91 - 120</TD></TR>"
- Print #1, "<TR><TD>" & rs!Specialty & "</TD><TD>" & rs!Business; Unit & "</TD><TD>" & rs!Cost; Center & "</TD><TD>" & rs!LOC_NAME & "</TD><TD>" & rs!Status & "</TD><TD>" & rs!Billing; ID & "</TD><TD>" & rs!Client_Name & "</TD><TD>" & rs!ops_level_1 & "</TD><TD>" & rs!ops_level_2 & "</TD><TD>" & rs!ops_level_3 & "</TD><TD>" & rs!ops_level_4 & "</TD><TD>" & rs!ops_primary & "</TD><TD>" & rs!client_level_4 & "</TD><TD>" & rs!client_primary & "</TD><TD>" & rs!Total; Of; Balance & "</TD><TD>" & rs![121 - 150] & "</TD><TD>" & rs![150+] & "</TD><TD>" & rs![61 - 90] & "</TD><TD>" & rs![91 - 120] & "</TD></TR>"
- rs.MoveNext
- Do
- Loop Until rs.EOF
- Print #1, "</TABLE></body></html>"
- Close #1
- End If
- End If
- End Sub