DataWright Information Services

Consulting and Resources for Excel and Access

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:\Test\Database3.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.