Using VBA to export report to Word (Access 2002 )

Topic

New Reply

I gotta keep my skills sharp by working on more databases. Anyway, it has been a while since I programmed a database in VBA and there was a report I used to have that I could program in VBA to automatically export the report as a .rtf file whenever I printed it because I work with blind people, so by automatically exporting it as a .rtf, I can then send the file to Braille embosser which accepts only Word documents.

Unfortunately, I lost that report and I can’t remember how to use VBA code to programatically export the report whenever the user clicks on “print” from the main menu that I borrowed from Helen Feddema. Anyone can help?

TIA!
Brent

Reply | Quote

Replies

Hi Brent, you’ll want to use DoCmd.OutputTo. If you look in the help file you’ll find a full description of it, but here’s an example:

DoCmd.OutputTo acReport, “rptName”, acFormatRTF, “C:ReportFileName.rtf”

Brent (gotta help someone with such a great name!)

Reply | Quote

Hello Brent!

Actually, that was what I tried to do (DoCmd.OutputTo…) but everytime I run the report, I get an error message saying that I can’t do that command while report is opening. I tried putting it in “OnOpen”, “OnPrint” and still get same error saying that I am not allowed to do so after I get the prompt to save the file.

The error message says:

“Run-time error 2585”
“This Action can’t be carried out while processing a form or report event.”

Reply | Quote

You’ll want to use this code in the form where you’re calling the report, not in the report itself. It sounds like Access won’t let you both open and export the report at the same time.

Brent

Reply | Quote

Unfortunately, I’m using Helen Feddema’s excellent switchboard program which allows you to choose from a variety of reports and either preview or print them. I can’t exactly call it from there, otherwise every reports would try to output.

However, I discovered a “placeholder” for the code called “On Page” which allows the report to open then run the code AFTER the data loading is complete. Instead of “On Open” which runs the code before the data is ready, I got it to work.

Thanks, Brent, from another Brent.

Reply | Quote

You can edit the code in the switchboard program so that there’s a third option — sending it to Word/RTF. That should be easier than editing each report.

First, add another check box to the option group fraReportMode for “Output to Word” and set its option value to 3.

Then change the following code in the the report button (cmdReports) from:

If Me![fraReportMode] = 1 Then
DoCmd.OpenReport ReportName:=strReportName, View:=acPreview
ElseIf Me![fraReportMode] = 2 Then
DoCmd.OpenReport ReportName:=strReportName, View:=acNormal
End If

to:

Select Case Me![fraReportMode]
Case 1
DoCmd.OpenReport ReportName:=strReportName, View:=acPreview
Case 3
DoCmd.OutputTo acOutputReport, strReportName, acFormatRTF
Case Else ‘2
DoCmd.OpenReport ReportName:=strReportName, View:=acNormal
End Select

Access will prompt you for where to save the RTF file.

Brent

Reply | Quote

I would caution you that RTF reports (BrentD’s suggestion) are often plagued with formatting problems that cause them to look quite different from an Access report.

Reply | Quote

Since we are using the RTF reports just to braille the reports, we can’t have fancy formatting because most of the formatting isn’t supported by the Braille embosser (You’d need to see Braille to see what I mean. Smile) so, we are creating reports with no formatting just for Braille and call them “[reportnameBraille]” so that the user knows that those can be outputted to Word as a RTF so that it can be embossed.

In any case, thanks to Wendell for the word of caution.

Reply | Quote

I have a program that copies reports to a central server for use by offices across the nation. Changing the file extension from .rtf to .doc has not caused any problems and makes is easier for the Word user to open the file.

Reply | Quote