Importing a large dataset into a single worksheet
Sometimes you will need to import a large number of records from
a database into Excel. If that dataset exceeds 65536 rows, you can’t
import it into a single continuous table, so you need to try
alternatives. They could include any of the following:
- Don’t import the data at all. Instead, create a pivot table
that uses the database table as an external data source. - Use multiple worksheets for the import.
- Place all data on one worksheet, successively filling up
columns as you go.
If you are using the Excel workbook for analysing the external
data, you may find that the first approach does everything you need.
If you need to see the raw data, some form of import is required,
but you will need to work out how to search or filter that data.
This article shows how to use the third approach to get the data
into Excel.
The code
To run this code you will need to
set a
reference to the Microsoft ActiveX Data objects 2.x Library,
where x is a number between 1 and 8. You will also need to change
the values of the three constants, to suit the name of your
database, the source data table, and the number of records to import
into each column.
Sub GetManyRows()
Dim MyConn
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim lngRec As Long
Dim intFld As Integer
Dim i As Integer, j As Integer, k As Integer
Dim fld As ADODB.Field
Const F_PATH = "C:TestDatabase3.mdb" 'path to your database
Const T_NAME = "tblLongDataSet" 'the source table for your data
Const CHUNK_SIZE = 65530 'number of records to import in each section
Set cnn = New ADODB.Connection
MyConn = F_PATH
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
'find how many rows there are.
'the cursor type is adOpenKeySet to prevent an error in ADO,
'where the RecordCount returns -1 for adOpenForwardOnly or adOpenDynamic
'keysets: http://support.microsoft.com/default.aspx/kb/194973
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=T_NAME, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'determine how many records to import, and set the value of k
'to do this correctly you must move to the last record in the recordset.
'note: k determines how many times to loop across to a new column.
rst.MoveFirst
rst.MoveLast
lngRec = rst.RecordCount
'determine how many fields there are in the recordset.
'by adding 1 to the number, we can create a blank column
'between the sections of imported data.
intFld = rst.Fields.Count + 1
'backslash division gives the integer portion of any division.
'e.g: 45000 65000 will return the value 0.
k = lngRec CHUNK_SIZE
'back to the start of the recordset, ready for import
rst.MoveFirst
'clear existing data on the sheet
ActiveSheet.Cells.ClearContents
'import the data, looping to fill additional columns as needed.
For j = 0 To k
'create field headers
i = 0
With Cells(1, 1 + j * intFld)
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With
'transfer data to Excel in sections defined by the CHUNK_SIZE constant.
Cells(2, 1 + j * intFld).CopyFromRecordset rst, CHUNK_SIZE
Next j
' Close the connection and clean up references
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Sub
Using the code
The comments in the code should be sufficient to describe what
each section of code is doing. To use the code, place it in a new
module (Alt+F11 to go to the VBE, Insert > Module, Paste, then Alt+Q
to return to Excel).
You can run it by pressing Alt+F8 and double-clicking the macro
name.
