Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automated Export of a query to Excel (2000 (9.0.4402 SR-1) )

    Somewhat puzzled about how if I manually export an 8,000 record query to Excel it takes about 15 seconds, yet when I perform the same action from a macro or VB it takes half an hour.

    Am I missing something here and has anyone else noticed this?

    Would appreciate some direction.

    Cheers,

    Niven <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Automated Export of a query to Excel (2000 (9.0.4402 SR-1) )

    Which method are you using in the macro or VBA code? TransferSpreadsheet or OutputTo?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated Export of a query to Excel (2000 (9.0.4402 SR-1) )

    I was using OutputTo, but have tried TransferSpreadsheet and that works a dream. Why I didn't use the latter originally, is because the macro asks for Table Name and I didn't realise you could stick a query name in there!

    Cheers for that, do you know why the OutputTo takes so long?

    Niven <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

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

    Re: Automated Export of a query to Excel (2000 (9.0.4402 SR-1) )

    TransferSpreadsheet exports data only from a table or query. OutputTo can export formatted output from all kinds of database objects. not just tables and queries. Because of the formatting, OutputTo has to do a lot more work than TransferSpreadsheet. But I can't explain why it takes so much longer in your situation.

Posting Permissions

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