OutputTo question

  • Thread starter Thread starter Presto
  • Start date Start date
P

Presto

I have the following in my code. It works as expected.
It exports the named table to an html file.

DoCmd.OutputTo acOutputTable, "tbl_MeetingDates", acFormatHTML,
"F:\html\MeetingDates.html"

What I would like to do is set it up to delete the old file, then export as
HTML and add my own HTML as its created. How can I have it export
basically a bunch of text - my html code - then the table data - then the
rest
of the html. I have several pages that I need to export this way and I'm
placing them in
one module together since they are only used once a month.

heres a simple example of what I want to output:

<html><head><title>Our Meeting Dates</title></head>
<body><P><H2>Our Meeting Dates</H2>
<HR> <P>

*** my table data goes here as text... ***
field1 field2 field3

<P><a href="mailto:[email protected]">Email Support</a>
</body></html>
 
Hi Presto

I don't think it is possible to have OutputTo *append* to an existing file.

I suggest you write a small function to append an existing text file to an
already open output file. The function would need to:
1. Open the existing file for input
2. Read each line until EOF is reached
3. Write each line to the output file number
4. Close the input file

You can then create a header file and a footer file for the before and after
bits and write code to:
1. Kill your final output file (if it exists)
2. Open your final file for output
3. Append your header file to the output file
4. Kill your temporary table file (if it exists)
5. OutputTo your table to the temporary file
6. Append the temporary file to the output file
7. Kill the temporary file
8. Append your footer file to the output file
9. Close the output file
 
Presto,

The good news is that you can get Access to do what you want. The
not-so-good news is that you are going to have to write everything yourself
rather than just having Access export the data with the SendTo command.


If you are of a certain age and can remember the original BASIC programming
languages, this is going to be deja vu all over again. Look up the Open and
Print # statements in the VBA online help. I copied the code below from the
Print # help text. Once you've opened your file, you will use the Print #
statement to write you HTML headers. Then open a recordset with your
data and write that in HTML format (you'll probably want to use a table.)
Lastly, close the output file


Open "TESTFILE" For Output As #1 ' Open file for output.
Print #1, "This is a test" ' Print text to file.
Print #1, ' Print blank line to file.
Print #1, "Zone 1"; Tab ; "Zone 2" ' Print in two print zones.
Print #1, "Hello" ; " " ; "World" ' Separate strings with space.
Print #1, Spc(5) ; "5 leading spaces " ' Print five leading spaces.
Print #1, Tab(10) ; "Hello" ' Print word at column 10.

' Assign Boolean, Date, Null and Error values.
Dim MyBool, MyDate, MyNull, MyError
MyBool = False : MyDate = #February 12, 1969# : MyNull = Null
MyError = CVErr(32767)
' True, False, Null, and Error are translated using locale settings of
' your system. Date literals are written using standard short date
' format.
Print #1, MyBool ; " is a Boolean value"
Print #1, MyDate ; " is a date"
Print #1, MyNull ; " is a null value"
Print #1, MyError ; " is an error value"
Close #1 ' Close file.


One other possibility, if writing the HTML code is a problem, is to use the
OutputTo command as you do now. Once you have your output file, you can try
opening it in Random mode and inserting your header HTML. I'm a little
rusty on this, so I'm sure whether you can do this or how. As a fallback,
you could create a new file, write your HTML, then open your exported file
and copy in the data row by row.


Hope this helps.
Scott

=============
 
Thank you Graham and Scott for your responses.
I believe both of these methods are too complicated for what I need.
I'm going to take the easy way out and set up a few report templates,
use a form to select the table or query I want, and have that data entered
on the report and export all that as html. This I can handle.

One more question and I'm off and running....
If the report ends up as multiple pages, it will export that way too.
Is there any way to make it a ONE page html document?
Will setting the print settings to something like 8 1/2 x 100 work?
... make Access think it is printing to one long page?
(the biggest report would be no longer than 6 pages)
 
Hi Presto

It really is not all that difficult.

Here is a function to append a textfile to an open file handle:

Public Function AppendTextFile( _
hOutFile As Long, _
sInFile As String _
) As Long
' Append the contents of a named text file
' to an already open file.
' Return the number of lines appended.
Dim hInFile As Long
Dim sLine As String
Dim iLines As Long
On Error GoTo ProcErr
hInFile = FreeFile
Open sInFile For Input As #hInFile
Do Until EOF(hInFile)
Line Input #hInFile, sLine
Print #hOutFile, sLine
iLines = iLines + 1
Loop
AppendTextFile = iLines
ProcEnd:
On Error Resume Next
If hInFile <> 0 Then Close #hInFile
Exit Function
ProcErr:
MsgBox Err.Description, vbExclamation, _
"Error appending file"
Resume ProcEnd
End Function

And here is some code to put the whole thing together:

Private Sub MyButton_Click()
Dim hOutFile As Long
Dim sOutFile As String
Dim sTempFile As String
On Error GoTo ProcErr
sTempFile = "F:\html\MeetingDates.tmp"
If Dir(sTempFile) <> "" Then Kill sTempFile
DoCmd.OutputTo acOutputTable, "tbl_MeetingDates", _
acFormatHTML , sTempFile
sOutFile = "F:\html\MeetingDates.html"
If Dir(sOutFile) <> "" Then Kill sOutFile
hOutFile = FreeFile
Open sOutFile For Output As #hOutFile
AppendTextFile hOutFile, "F:\html\MD_Header.html"
AppendTextFile hOutFile, sTempFile
AppendTextFile hOutFile, "F:\html\MD_Footer.html"
ProcEnd:
On Error Resume Next
If hOutFile <> 0 Then Close #hOutFile
Kill sTempFile
Exit Sub
ProcErr:
MsgBox Err.Description, vbExclamation, _
"Error creating MeetingDates.html"
Resume ProcEnd
End Sub
 
Mr Bass had a much easier idea that works perfectly and is easy to set up.

Here's what I did:
1. Create the header.txt and footer.txt files with the necessary html tags
for top and bottom of file.
2. Create small batch file to concatenate the files as Mr Bass suggested.
- attach to a macro called "MakeHtmlFile"
3.Create reports that have the fields I need, enclosed by needed html tags :
in an unbound field: ="<tr><td>"&Field1&"</td><td>
"&Field2&"</td></td>"
4. Export data using VBA:
DoCmd.OutputTo acOutputReport, "ReportName_HTML", acFormatTXT,
"c:\pathtofile\body.txt"
DoCmd.RunMacro "MakeHtmlFile"
MsgBox "Export Complete!", vbOKOnly

Sweet !
 
Back
Top