Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using VBA to export report to Word (Access 2002 )

    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

  2. #2
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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!)

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    Hello Brent! [img]/forums/images/smilies/smile.gif[/img]

    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."

  4. #4
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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. [img]/forums/images/smilies/smile.gif[/img]

    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.

  6. #6
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 Posts

    Re: Using VBA to export report to Word (Access 200

    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.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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.

  9. #9
    Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBA to export report to Word (Access 200

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •