Share via

Displaying Data from a Recordset in an Excel Worksheet

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The example uses ADO Connections and recordsets to retrieve data. When the worksheet is activated, the code creates a connection object, creates a Recordset object, and uses the data to fill the cells of the worksheet.

To try the example

  1. Open a new Excel Workbook project.

  2. Open the Visual Basic Editor (ALT + F11).

  3. On the Tools menu, click References. Select the Microsoft ActiveX Data Objects 2.1 Library, and click OK.

  4. Paste the example code into the Sheet1 code window.

    The example code uses the Active event for demonstration purposes only. When creating a real solution, you can encapsulate the code in a procedure to be used in an Excel macro.

  5. Switch back to Excel.

  6. Click the Sheet2 tab.

  7. Click the Sheet1 tab (to activate the worksheet).

Example Code

Option Explicit
' Declare object variables for the Connection and Recordset objects.
Private cnNwind As Connection
Private rsCustomers As Recordset

Private Sub Worksheet_Activate()
   ' Instantiate the object.
   Set cnNwind = New Connection
   ' Confirm the existence of the Northwind sample database in the path
   ' below!
   cnNwind.ConnectionString = _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Persist Security Info=False;Data Source=" & _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   cnNwind.Open
   ' Instantiate the Recordset object, then set its properties.
   Set rsCustomers = New Recordset
   With rsCustomers
      .Source = "SELECT * FROM Customers"
      Set .ActiveConnection = cnNwind
      .CursorLocation = adUseClient
      .CursorType = adOpenStatic
      ' Open the recordset.
      .Open
   End With
   
   ' The code below uses the ADO Fields collection to fill cells on
   ' the worksheet. The code also uses the Worksheet object's Cells
   ' object to reference the columns and rows.
   ' RowCnt is a counter for rows in the worksheet. FieldCnt is a
   ' counter for the number of fields in the recordset.
   Dim RowCnt, FieldCnt As Integer
   RowCnt = 1
   ' Use field names as headers in the first row.
   For FieldCnt = 0 To rsCustomers.Fields.Count - 1
      Cells(RowCnt, FieldCnt + 1).Value = _
      rsCustomers.Fields(FieldCnt).Name
      Rows(1).Font.Bold = True
   Next FieldCnt
   
   ' Fill rows with records, starting at row 2.
   RowCnt = 2
   
   While Not rsCustomers.EOF
      For FieldCnt = 0 To rsCustomers.Fields.Count - 1
         Cells(RowCnt, FieldCnt + 1).Value = _
         rsCustomers.Fields(FieldCnt).Value
      Next FieldCnt
      rsCustomers.MoveNext
      RowCnt = RowCnt + 1
   Wend
   cnNwind.Close
End Sub