need VBA code to split and export the table to multiple excel

[Giri.hb]

Hello Team,

I have a table in the access which is having more than 10 lakh rows, So I need the code to export the data to excel by a specific range of rows. For example I need to export 65000 rows for some work book and 75000 or more for other work books. Please help me to get the code to split and export the data to the multiple work books at the earliest possible.

Regards,
Girish

[ranman256]

The table that has the data must have an extra column called Exported.
Make and run query, qsRecs2Export, that pulls TOP 65000 where EXPORTED = false.
export this to exel file.

Then run a query that runs an update using, qsRecs2Export, then sets EXPORTED = true
this marks the data as exported and wont happen again.

Then run qsRecs2Export again to get the next batch, and repeat until all records are marked true.

[Giri.hb]

Hello ranman256,

Can u please assist me on how to do this with step by step guide, since I am new to the access. Kindly do the need full, since its quiet urgent for me. Thank you.

[ranman256]

put a field in your table boolean EXPORTED.

build a query named qsRecs2Export,
select TOP 65000 from table where EXPORTED = false.

export this query using:
transferSpreadsheet

then run a query to mark the EXPORTED items as true, quMarkExported
update qsRecs2Export set EXPORTED = TRUE

PUT these 2 steps into a macro
1. transerspreadsheet
2. quMarkExported

Then:
Run the macro
edit the macro , change the export workbook name
then run the macro again
repeat until ALL EXPORTED fields are true