Output Record to HTML file

  • Thread starter Thread starter na3
  • Start date Start date
N

na3

Greetings everyone,

I'm an experienced beginner which I guess means that I
really don't know too much but I've been setting up a DB
finding answers and ways to get things done. These forums
have been of great help along with Viescas' book. But I'm
trying to do something that just isn't happening....

I want to create small individual HTML files out of each
record in my inventory table based on the PartID field.
The prospect of creating 6000 files by hand has got me
thinking that surely, Access 97 can do this for me. I've
played with the macro OutputTo command with no success.
Should this be done out of a query or a report? Is this
something that is probably way over my head or not even
possible?

I've created a calculated query field that I'm satisfied
with but how do I get each individual record out to their
own HTML file in a clean manner labeled by PartID.html
where I will not have go back to touch them up at all?

Any help would be greatly appreciated.

na3
 
I want to create small individual HTML files out of each
record in my inventory table based on the PartID field.
The prospect of creating 6000 files by hand has got me
thinking that surely, Access 97 can do this for me. I've
played with the macro OutputTo command with no success.
Should this be done out of a query or a report? Is this
something that is probably way over my head or not even
possible?

I've created a calculated query field that I'm satisfied
with but how do I get each individual record out to their
own HTML file in a clean manner labeled by PartID.html
where I will not have go back to touch them up at all?
Click to expand...

If I read you right, the calculated field returns the HTML code for one
of your little files (if not, post back). I'll assume that the name of
the field is HTML and that the query also contains a numeric PartID
field.

What you want can't be done with a macro but it's not too difficult in
VBA. You open a recordset on your query, and iterate through each record
in the recordset, creating a textfile as you go.

Something like this UNTESTED air code; create a new VBA module and paste
it in:

Sub CreateHTMLFiles()
Dim dbD as DAO.Database
Dim rsR as DAO.Recordset
Dim varHTML as Variant
Dim strPartID As String
Dim lngResult As Long

Const FILEPATH = "D:\Folder\Subfolder\"
'Initialise
Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("MyQuery")

'Iterate through recordset
Do While Not rsR.EOF
varHTML = rsr.Fields("HTML").Value
strPartID = CStr(rsr.Fields("PartID").Value
'write to file using custom function below
lngResult = WriteToFile(varHTML, _
FILEPATH & strPartID & ".HTML"
If lngResult <> 0 Then
Debug.Print "Problem creating file for " _
& strPartID
End If
rsr.MoveNext
Loop

'Tidy up
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.

'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.

Dim lngFN As Long

On Error GoTo Err_WriteToFile
lngFN = FreeFile()
'Change Output in next line to Append to
'append to existing file instead of overwriting
Select Case Overwrite
Case True
Open FileSpec For Output As #lngFN
Case False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top