Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access report to Excel (Access 2002 / Excel 2002)

    I have a report in an Access database that I want to kick out to excel. The report contains the main report and two sub reports both located in the detail section of the main report. My goal is to have the report in excel formatted and structured exactly as it is in the access report. However when I use the OutputTo (code below) command the resulting excel file contains no formatting and the data is translated into subtotals, not all matching the structure of the source access report. If I kick the same report out to rich text format the report is exactly the same, minus the company logo (which I expected in rtf format). This report is a proto-type, I really don't want to take the time to write the VBA in excel to duplicate the Access report. Unfortunately, they have to have the report is excel, pdf or rtf is not an option. So, I'm looking for a simpler solution. My question is two parts.

    #1
    Is there a way to send the report directly to excel and maintain the formatting and structure of the source report, without doing any VBA in excel?

    #2
    If there's no easy solution to #1, is there a way to pull the rtf file in to excel and maintain the format and structure? I realize with this solution I may have to write some VBA in excel to populate an image control with the company logo.

    OutputTo

    DoCmd.OutputTo acReport, stDocName, acFormatXLS, stFileName " & Forms![frmMnfstSmryRprt]![txtMnfstID] & ".xls", -1


    Any help would be greatly appreciated...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report to Excel (Access 2002 / Excel 2002)

    I'm afraid I'm not going to tell you what you wanted to hear.

    A report is an output format intended to be viewed and printed. It isn't suitable to be processed in another application.

    Exporting a report directly to Excel doesn't work well, as you have found out, and export to RTF is one of the weakest features of Access, it hasn't been updated since version 1, I think.

    You can import tables and queries from Access into Excel, and build a report from them.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access report to Excel (Access 2002 / Excel 2002)

    I was afraid of that. Can you point me in the direction of any good sample excel spreadsheets that demonstrate using Access tables/queries to build a report? I'm looking to defend my position that an Excel file is not the best choice of format (at this point) for distribution of this report. I'm having trouble proving something that seems self-evident.

    Thanks for the post Hans.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access report to Excel (Access 2002 / Excel 2002)

    Building a report in Excel based on Access data is not different from building a report based on native Excel data. The main point is that it's a lot of extra work to recreate a report in Excel while it isn't really necessary. If you want to distribute an Access report in electronic form, the best choices are

    1) Snapshot format (.snp).
    All versions of Access have built-in support for exporting a report to snapshot format. This preserves all formatting exactly as it is in Access.
    To open and print a snapshot file, users need the free Snapshot Viewer from Microsoft. Users don't need to have Access itself installed.

    2) PDF format (.pdf)
    Access (in versions before 2007) does not have built-in support for exporting to PDF, but there are many free PDF writers, and Stephen Lebans has a free ReportToPDF tool. Almost everyone nowadays has a PDF viewer installed, for example Adobe Reader.

Posting Permissions

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