splitting a large access table to export to excel

  • Thread starter Thread starter Jma000
  • Start date Start date
J

Jma000

I have 100,000 records and need to export the table to excel. excel
only handles 65,000 or so. I was able to export the 65,000. How do I
export the remaining 35,000? Is there a query that can pull the
remaining 35,000 into a table?

Copy the last 35,000 takes too long.

Thanks.
 
Hi Jma,

If you use TransferSpreadsheet rather than OutputTo you can define
worksheets, and export the remaining records to a second sheet in the same
workbook. You could either break the table up using queries and export
those, or use a loop to count each record as it's written to Excel, and when
you hit the limit for the first sheet (65535), stop that DoCmd action and
begin another to write to a different sheet in the same file.
 
I have 100,000 records and need to export the table to excel. excel
only handles 65,000 or so. I was able to export the 65,000. How do I
export the remaining 35,000? Is there a query that can pull the
remaining 35,000 into a table?

Copy the last 35,000 takes too long.

Thanks.
Click to expand...

Can you wait until the next version of Excel comes out? :-) It will
handle over 100,000 records.

I suggest that you can add (if you don't already have) an autonumber
field and then use a query to select the records you want for each export.
Note: normally I would now recommend autonumber for this as it tends to
leave gaps etc, but for this use and for one use, it should work fine.
 
Back
Top