Access to Excel export > 65,536 rows

[Annette]

How do you export date from an Access table to excel that contains more than
65,536 rows.

How do you put the data onto separate worksheets?

[John Nurick]

Hi Annette,

One way is to export the first X records to one worksheet, the next to
another, usw., by using code to construct and execute a series of SQL
queries like the following:

SELECT CUST, ITEM, BT_ORG2, AMOUNT
INTO [Excel 8.0;HDR=Yes;Database=C:\Temp\XXX.xls;].[Sheet2]
FROM
(SELECT
(SELECT COUNT(*) FROM CAA_MOD AS C
WHERE C.ID <= T.ID) AS SEQ,
ID, CUST, ITEM, BT_ORG2, AMOUNT
FROM CAA_MOD AS T)
WHERE (SEQ >=11 AND SEQ <=20)
ORDER BY ID;

The VBA 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 ID;"

Const CHUNKSIZE As Long = 50 'records per sheet

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.