Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Export To Excel (2003 SP2)

    I've set up a plain report, no headers, footers or superfluous labels, and no formatting, for the sole purpose of exporting data to Excel. It does however have sub-reports up to three levels deep. As I was creating it and doing progressive testing, it exported almost instantaneously using the Office Links button, even when the final Access report had only 34 pages. I then setup an "Output To" macro but when I tested that, Access displayed an information message exporting literally thousands of pages, around 33,000 of them! Also, when I tried the standard Office Links button on the main toolbar, it again exported over 30,000 pages.

    Has anyone encountered this before? Any ideas as to what's going on?

    Thanks

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

    Re: Auto Export To Excel (2003 SP2)

    What is the result? Lots of data, or lots of empty space?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Export To Excel (2003 SP2)

    It's all the same data - nothing more, nothing less. But I discovered that by using the OfficeLinks button on the tool bar to export to Word, it does the job instantly. Then if I use the same button to export to Excel, it works instantly again. However, as soon as I try the Output To macro, it goes through the thousands of pages routine.

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

    Re: Auto Export To Excel (2003 SP2)

    I think we'd need to see (a stripped down copy of) the database.

    Added later: have you tried the TransferSpreadsheet action/method? Does that make a difference?

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Export To Excel (2003 SP2)

    I got it to work instantly but not without trial and error. I tried setting the control source for the main report and all sub-reports first on the relevant table, then on a SQL query, then on a saved query, but none of that made any difference. The actual solution was unexpected: I reduced the top and bottom report margins to absolute minimum and it now exports instantly! The total combination of all original reports actually made the final report very close to the maximum length allowed so I guess decreasing the margins gave it some "brain space", if you know what I mean!

    Thanks again.

Posting Permissions

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