Hi Sandy,
This should get you started:
How to export the first X records to one worksheet, the next to another,
usw.
The idea is to use VBA code to construct and execute a series of
queries, each of which would be similar to this (which generates a
sequential "record number"):
SELECT Field1, Field2, Field2
INTO [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Sheet2$]
FROM
(SELECT
(SELECT COUNT(*) FROM MyTable AS C
WHERE C.ID <= T.ID) AS SEQ,
Field1, Field2, Field3
FROM MyTable AS T)
WHERE (SEQ >=1 AND SEQ <=10000)
ORDER BY Field1;
The VBA code would be something like this (untested air code):
Dim strSQL
Dim strFileSpec As String
Dim strSheetBaseName As String
Dim lngSheetNumber As Long
Dim lngRecCount As Long
Dim lngFirstRec As Long
Dim dbD as DAO.Database
Set dbD = CurrentDB()
'Elements of SQL string for query
'replace ... with the actual SQL stuff needed
Const SQL1 = "SELECT ... Database="
Const SQL2 = "] FROM ... WHERE (SEQ>="
Const SQL3 = ")ORDER BY PK;"
Const CHUNKSIZE As Long = 10000
strFileSpec = "C:\Folder\Filename.xls"
strSheetbaseName = "Sheet"
lngSheetNumber = 1
'Get number of records to export
lngRecCount = DCount("*","MyQuery")
lngSheetNumber = 1
lngFirstRec = 1
Do
'Assemble the SQL string for the query
strSQL = SQL1 & strFileSpec & ";].[" _
& strSheetBaseName & Format(lngSheetNumber, "000") _
& SQL2 & Cstr(lngFirstRec) & ") AND (SEQ<=" _
& Cstr(lngFirstRec + CHUNKSIZE) & SQL3
'execute it
dbD.Execute strSQL, dbFailOnError
lngSheetNumber = lngSheetNumber + 1
lngFirstRec = lngFirstRec + CHUNKSIZE
Loop Until lngFirstRec > lngRecCount
