Microsoft Office 2000 Developer |
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
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.
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