Import extrenal data exceeds 65536
 

A few day ago, the question of how a recordset that has more rows than Excel
can be spanned into multiple worksheets. I attach the code for the suggestion
I had made then.
Note: I have use an Excel data source: modify code to use any source. I have
selected 20000 rows as the maximum- in practice, this will be 65536 (Row 1
contains field names).

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