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
To run this code you will need to
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