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.

DataWright Information Services

Consulting and Resources for Excel and Access

Importing a large dataset into a single worksheet

The code

Using the code