Transfer Large Access table records to Many Excel Worksheets

  • Thread starter Thread starter Tiffany via AccessMonster.com
  • Start date Start date
T

Tiffany via AccessMonster.com

I have a CSV file that has 1,286,881 records on it. I need to put them in an
Excel file. Excel will only take 65553 records. So, I imported the CSV into
Access just fine, but how do I run a macro or query that will transfer a 65,
000 number of records each o Excel?

I need to tell it to send 65,000 to Excel worksheet1 , then send the next 65,
000 to Excel worksheet2, etc, etc,.. up to 19 - 20 worksheets within one(1)
Excel Workbook.


Please help,

Thanks,
Tiff
 
Tiff,
I think you may be trying to force a round peg into a square hole.
Excel is not designed to be a data warehouse... its primary function is
data analysis. Access is designed to be a user-friendly mid-level data
warehouse with basic reporting capabilities.
Technically speaking, your problem is easy to overcome, but I would
first ask why you are trying to perform this task. The only reason you
can have for dumping 65K records into Excel is to analyze bulk data
(run pivots, charts, stat analysis, etc). If this is the case, I would
recommend simply running queries and pivot tables on your access data
from Excel. You can create pivot tables and queries to 'get data from'
Access and simply summarize it in excel.

If this is not what you end goal is, then I would recommend dropping an
autoincrement field into your Access data table and then query the
table using the auto increment field as range criteria "Between 1 And
65000" , "Between 65001 And 130001", etc...
After you create your query, simply copy and paste away.

If you need a repeatable quick way to make this work I would recommend
that you create Excel based queries with the same range criteria that
can simply be refreshed at your convenience.

If none of these options works for you then you will need to look into
VBA programming for a better answer. Unfortunately, VBA is not my
specialty.

Bryan
 
Back
Top