rs.filter "Filter cannot be opened"

[Guest]

I have a simple excel table that is converted to a recordset. The RS field
names are "COLA", "COLB", "COLC"... etc.
I can output the field names and the complete RS to a worksheet.

My problem is I can't filter the RS.

Here's the code I use to output the data:
=============================
'output headers
For lngC = 0 To RS.Fields.Count - 1
rngCell(1, lngC + 1).Value = RS.Fields(lngC).Name
Next

RS.MoveFirst

'output the complete RS data - starting the next row
rngCell(2).CopyFromRecordset RS, 1
=============================

I tried this code, but it was not successful

RS.MoveFirst
RS.Filter = "[COLB]='17'"

rngCell(2).CopyFromRecordset RS, 1

** The error message is "Filter cannot be opened" ** when executing the
RS.Filter code.

Can Excel do RS filtering, sorting, or finding?
Is my recordset incorrectly defined?
I've referencing MS ADO 2.8 library

Thanks
- Pat

 

[Guest]

I have a simple excel table that is converted to a recordset. The RS field
names are "COLA", "COLB", "COLC"... etc.
I can output the field names and the complete RS to a worksheet.

My problem is I can't filter the RS.

Here's the code I use to output the data:
=============================
'output headers
For lngC = 0 To RS.Fields.Count - 1
rngCell(1, lngC + 1).Value = RS.Fields(lngC).Name
Next

RS.MoveFirst

'output the complete RS data - starting the next row
rngCell(2).CopyFromRecordset RS, 1
=============================

I tried this code, but it was not successful

RS.MoveFirst
RS.Filter = "[COLB]='17'"

rngCell(2).CopyFromRecordset RS, 1

** The error message is "Filter cannot be opened" ** when executing the
RS.Filter code.

Can Excel do RS filtering, sorting, or finding?
Is my recordset incorrectly defined?
I've referencing MS ADO 2.8 library

Thanks
- Pat

Both of these work for me:


Sub testADO()

Set rngCell = Sheet1.Range("A1")

Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset

Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myBook.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Rst.Open "Select * FROM [Sheet1$]", _
Cnn, adOpenStatic, adLockOptimistic, adCmdText

Rst.Filter = "[COLB] = '17'"

For lngC = 0 To Rst.Fields.Count - 1
rngCell(1, lngC + 1).Value = Rst.Fields(lngC).Name
Next

rngCell(2).CopyFromRecordset Rst


End Sub


Sub testADO1()

Set rngCell = Sheet1.Range("A1")

Dim Cnn As New ADODB.Connection
Dim Rst As New ADODB.Recordset

Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myBook.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

Rst.Open "Select * from [Sheet1$] where COLB = 17", _
Cnn, adOpenStatic, adLockOptimistic, adCmdText

For lngC = 0 To Rst.Fields.Count - 1
rngCell(1, lngC + 1).Value = Rst.Fields(lngC).Name
Next

rngCell(2).CopyFromRecordset Rst


End Sub