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

    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.

    Thanks,

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

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Try with;
    DoCmd.OutputTo...........

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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:
    http://www.mvps.org/access/modules/mdl0006.htm
    http://msdn.microsoft.com/library/de...sofficedev.asp
    http://support.microsoft.com/kb/q302460/

    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.

    Cheers,
    tc

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    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
  •