ExcelBanter - Import extrenal data exceeds 65536

Here's some code, annotated and tested: I have used a WORKBOOK as the source
data, modify the code to use another source.

Sub Partition2()
'Get the Source data
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\ajay2.xls;Extended Properties=Excel 8.0;"
Sql = "SELECT * FROM [Sheet1]"
Set ADORS = CreateObject("ADODB.RecordSet")
'Number of records in a single sheet
ADORS.PageSize = 20000
ADORS.Open Sql, Cnn, 1, 3 ' adOpenKeySet, adLockOptimistic
'Create Excel Workbook ...
Set xl = CreateObject("Excel.Application")
'Hold user's default number of sheets
UserWBS = xl.SheetsInNewWorkbook
With ADORS
'... with the right number of worksheets to hold all records
xl.SheetsInNewWorkbook = .PageCount
xl.Workbooks.Add
xl.SheetsInNewWorkbook = UserWBS
xl.Sheets.Select
xl.Sheets(1).Activate
'Add field names to Row 1 in all sheets
For i = 0 To .Fields.Count - 1
xl.Cells(1, i + 1).Select
xl.Selection.Font.Bold = True
xl.Selection.FormulaR1C1 = .Fields(i).Name
Next
'Populate each sheet in turn
For i = 1 To .PageCount
xl.Sheets(i).Select
xl.Range("A2").CopyFromRecordset ADORS, .PageSize, 256
Next
xl.Sheets(1).Select
xl.Sheets(1).Range("A1").Select
End With
' Tidy up
ADORS.Close
Set ADORS = Nothing
' Make Excel visible, user must decide to save it or not
xl.Visible = 1
Set xl = Nothing
End Sub

"Jamie Collins" wrote:

"AA2e72E" wrote ...

I don't have any sample code to hand: I can suggest some ideas.

ADO has a pagesize
(no of rows) property which you can set to 65536 & then loop through the
PageCount property to retrieve all records.

Could you post some code that demonstrates how to this, please?

Many thanks,
Jamie.

--