OutputTo question [WiredBox.Net - Office Newsgroups]

Group: Microsoft Access » microsoft.public.access.formscoding
Thread: OutputTo question

OutputTo question

"Presto" <presto__NOSPAM[ at ]prestoweb.net> 11/12/2008 8:17:05 PM

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:me[ at ]myserver.com">Email Support</a>
</body></html>

Re: OutputTo question

"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> 11/12/2008 9:18:05 PM

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
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
news:OdBieOQRJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text]
>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:me[ at ]myserver.com">Email Support</a>
> </body></html>
>

Re: OutputTo question

"Scott Lichtenberg" <donot[ at ]reply.com> 11/12/2008 9:33:01 PM

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

=============

"Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
news:OdBieOQRJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text]
>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:me[ at ]myserver.com">Email Support</a>
> </body></html>
>

Re: OutputTo question

"Presto" <presto__NOSPAM[ at ]prestoweb.net> 11/12/2008 10:00:43 PM

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)

"Scott Lichtenberg" <donot[ at ]reply.com> wrote in message
news:%23IAv%234QRJHA.4408[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text]
> 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
>
> =============
>
> "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
> news:OdBieOQRJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
>>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:me[ at ]myserver.com">Email Support</a>
>> </body></html>
>>
>

Re: OutputTo question

"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> 11/13/2008 12:01:55 AM

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

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
news:urj2aIRRJHA.5092[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text]
> 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)
>
> "Scott Lichtenberg" <donot[ at ]reply.com> wrote in message
> news:%23IAv%234QRJHA.4408[ at ]TK2MSFTNGP03.phx.gbl...
>> 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
>>
>> =============
>>
>> "Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
>> news:OdBieOQRJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
>>>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:me[ at ]myserver.com">Email Support</a>
>>> </body></html>
>>>
>>
>
>

Re: OutputTo question

"Presto" <presto__NOSPAM[ at ]prestoweb.net> 11/15/2008 8:38:51 PM

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 !

"Clifford Bass" <CliffordBass[ at ]discussions.microsoft.com> wrote in message
news:6AE32F02-BD9C-41D0-A66A-D8E10AE4C1B7[ at ]microsoft.com...
[Quoted Text]
> Hi Presto,
>
> How about creating a header file and a footer file that contain the
> parts you want to add to the file. Then create a batch file that you can
> call after the table is exported. The batch file would use the copy
> command to combine the three files. The batch file might contain
> something like this:
>
> copy
> "F:\html\Header.html"+"F:\html\MeetingDates.html"+"F:\html\Footer.html"
> ""F:\html\Meeting Dates with Header and Footer.html"
>
> This is untested. And I am sure there are other ways to do it.
>
> Clifford Bass

"Presto" <presto__NOSPAM[ at ]prestoweb.net> wrote in message
news:OdBieOQRJHA.1448[ at ]TK2MSFTNGP04.phx.gbl...
>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:me[ at ]myserver.com">Email Support</a>
> </body></html>
>

Home | Search | Terms | Imprint Contact

Newsgroups Reader - provided by WiredBox.Net