Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    12

    Unanswered: Export Access 97 reports to Excel

    I am trying to export Access 97 reports to a excel spreadsheet. The catch is I need to export more than 1 table to a excel spreadsheet. I know that using TransferSpreedsheet I can export multiple tables and queries to a excel spreadsheets, but how do I export reports? I have tried creating a Macro and using Output to, but that will create a seperate excel spreadsheet for every report.

    Is their anyway to do this? Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    I would never export a report to excel, you lose all your formating. What you will get will look like a query anyway, but probably not in the order you want.

    So what you want to do is create one query with all the information you want included to export that. As far as the process on exporting you could use a macro to do so, or when view the query in datasheet view just click on File, Export adn specify the file type as excel

    S-

  3. #3
    Join Date
    Nov 2003
    Posts
    12
    Yes I realize the formatting gets messed up, but thats acceptable. I have created a Macro that exports a query with everything in the report to Excel, BUT the report groups up the categories and totals up the catogries, and I cant do this in a query.

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    When viewing the report "File, Export adn specify the file type as excel"

    Or write VB Code to export your reprt (don't know it off top of my head or would post it for you).

    S-

  5. #5
    Join Date
    Nov 2003
    Posts
    12
    Yeah I can manually export the reports to excel, but thiers about 50 reports and this has to be done weekly, so thats not happening.

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    Look up "OutputTo Action" in the help

    S-

  7. #7
    Join Date
    Nov 2003
    Posts
    12
    As stated in my original message I have already tried "Output to" action, it works, BUT it doenst allow me to export multiple reports to 1 excel file, it will only export each report to its only excel file.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    If your Reports are based on queries and they have the same Fields then you can:
    1. Copy the first Query and convert it to Creat Table Query. For example you could create the table "allMyData"

    2. Copy from the secound Qurey to the last one, and convert them to Add to Table Query i.e "INSERT INTO SELECT FROM"

    3. Run the converted queries
    4. Use the OUTPUT TO action to Export your table allMyData

    Hope I could express what I meant.

    Good luck

  9. #9
    Join Date
    Nov 2003
    Posts
    12
    K thanks for the idea, and its a good one, but it doenst solve my problem. Getting the queries into 1 excel file isnt my problem, I can easily do that with the "TransferSpreadsheet" action in a Macro. I need to get Reports into 1 excel file. My reports group by the fields and compute some calculations for me, so if I just using the query they are based on I lose that stuff.

    So unless I just miunderstand your idea, it wont solve my problem.

  10. #10
    Join Date
    Nov 2003
    Posts
    267
    Another Option to try

    Embedd all your 50 reports into One reports as sub reports. and Output this one report to excel

    If you need each report on seperate sheet you could try (with VB) outputing each repoert seperately and with either VB in Access or Excel write a routine that combines all you individual excel worksheets into One master file (it is possible, but it has been a long time since I have done this)

    S-

  11. #11
    Join Date
    Nov 2003
    Posts
    12
    Yeah thats what I ended up doing this morning. I just created another report with a bunch of subreports and added pagebreaks between the subreports. Then I just used a marco action "Output To" to export the report(s) to excel. Works great.

    Thanks!!

  12. #12
    Join Date
    Apr 2011
    Posts
    4
    Does that not cause performance issues when trying to bring over that many reports? I would be weary of the privacy implications as well.

    -Grahm
    Excel Reports

Posting Permissions

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