Programming Excel with VBA and .NET

Name

recordset.Filter[= setting]

Synopsis

Sets or returns a filter for the recordset. You can use filters to work with different sets of data in a table without having to open separate recordsets. The following code adds product names for all beverages from the Products table in the Northwind Traders sample database to the first column of the current worksheet:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strDbPath As String
Dim intIdx As Integer
 
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
strDbPath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strDbPath
 
cnn.Open
rs.Open "Products", cnn, adOpenStatic, adLockReadOnly, adCmdTable
rs.Filter = "CategoryID = 1"
 
rs.MoveFirst
intIdx = 1
Do Until rs.EOF
    strName = rs!ProductName
    ActiveSheet.Cells(intIdx, 1) = strName
    rs.MoveNext
    intIdx = intIdx + 1
Loop
 
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing