Exporting Query to Multiple Excel Files with No More than 20,000 Rows Each

[jmathews607]

Hi -

I have a query that results in about 110,000 records of names and email addresses to be uploaded into an email marketing service. We can only upload Excel spreadsheets with no more than 20,000 rows at a time. Does anyone know of a way to automate splitting and exporting a query into Excel files with 20,000 rows or less?



Thanks!

[June7]

All options I can imagine involve VBA procedure.

1. need a numeric unique ID field in table (autonumber ?), ideally with no gaps in sequence, apply filter that increments the ID range in a loop, apply filter to a QueryDef or a report

2. open a recordset of all records and in a loop write 1 record at a time, hit 20,000 and repeat the loop but into a new worksheet