Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006

    Unanswered: Exporting Access Tables to Excel(Please help!)

    Please need help,

    I am trying to copy large amount of Data well over 65535 lines from Access's table to Excel via VBA. Since the max number of rows in excel sheet is 65536, how can I transfer the data? Is it possible to use DoCmd.Transferspreadsheet and transfer 65535 lines at a time on each loop iteration till the .RecordCount has reached eof and transfer each of 65535 lines to new .Worksheet(i)?
    Please if you have any advice I greatly appreciate.


  2. #2
    Join Date
    Oct 2003
    Provided Answers: 1
    Try to split your data into small tables then export them to Excel...

  3. #3
    Join Date
    Apr 2005
    Zagreb - Croatia
    Try with;

  4. #4
    Join Date
    Apr 2004
    outside the rim
    OutputTo has a limitation of a little over 16,000 and i think TransferSpreadsheet does as well.

    If there's an aspect of the data that allows you to break it into small chunks (such as a date range) then you can loop through the range of chunks and export each individual chunk.

    To get multiple sheets in one book is not hard, but will require using code to utilize the Excel object (the dreaded "Automation"). It's actually pretty easy to do, but a lot of stuff to type in here.

    Try these:

    Something I noted a while back: if you Create and Excel object in code, then release the object but don't close Excel, you may get an error later when the user finally closes Excel. It's a quirk, but I make sure it never happens by requiring the user to have Excel open before running the code and using GetObject instead of CreateObject. I don't know if they fixed this in 2K and newer.


  5. #5
    Join Date
    Apr 2006
    Thank you for your replies,

    In fact I followed the hammbakka suggestion, and divide the table to smaller size (65536 of records) and transfer the data one at a time to different worksheet and that worked out very well.

    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