Hi Eric,
There are several ways of doing it. One is to modify your query to
generate sequential record numbers and use this as the core of a
make-table query that creates an Excel worksheet. Then execute this once
for each 50 records, using appropriate record numbers and sheet names.
Here's a query that does this, from my test database. PK is the primary
key of the underlying table (tblT - replace this with the name of your
query), SEQ is the calculated record number field (doing this means that
it doesn't matter if the primary key is non-sequential or non-numeric).
SELECT PK, ITEM, DESCRIPTION, AMOUNT
INTO [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Sheet2]
FROM
(SELECT
(SELECT COUNT(*) FROM tblT AS C
WHERE C.PK <= T.PKD) AS SEQ,
ID, CUST, ITEM, DESCRIPTION, AMOUNT
FROM tblT AS T)
WHERE (SEQ>=11) AND (SEQ<=20)
ORDER BY PK;
The VBA code would be something like this air code:
Dim strSQL
Dim strFileSpec As String
Dim strSheetBaseName As String
Dim lngSheetNumber As Long
Dim lngRecCount As Long
Dim lngFirstRec As Long
'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 = 50
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
DBEngine(0).(0).Execute strSQL
lngSheetNumber = lngSheetNumber + 1
lngFirstRec = lngFirstRec + CHUNKSIZE
Loop Until lngFirstRec > lngRecCount
