CopyFromRecordset Ignores Filters

[Ron Mittelman]

Help!!

I'm trying to paste ADO recordset data into an excel spreadsheet from VB6.
The syntax I'm using is:
Range.CopyFromRecordset MyAdoRecordset
where Range is a single Excel cell, and MyAdoRecordset is, well, an ADO
recordset.

This normally works perfectly. However, if the recordset is filtered, the
filter is ignored. For example, the recordset has 20 records. I apply a
filter to the recordset. When I query the AbsolutePosition & RecordCount
properties in the immediate window, I get 1 and 3 (depending on filter)
respectively, as expected. So far so good. When I execute the
CopyFromRecordset method, the spreadsheet contains all 20 rows. Very curious.

Does anybody know why this happens, and how to prevent it?

 

[Robin Hammond]

Ron,

I was about to say this doesn't happen to me then I went back and checked
some of my code. There's a problem in Excel 97 with the CFR method. See
below for a workaround depending on version:

Sub OutputRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
' Author : Robin Hammond
' Purpose : dumps a recordset to a new workbook
'---------------------------------------------------------------------------------------

Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long
Set W = ActiveWorkbook
Workbooks.Add
With rsName
For nField = 1 To .Fields.Count
Cells(1, nField).Value = .Fields(nField - 1).Name
Next nField
If .RecordCount = 0 Then Exit Sub
.MoveFirst
If Not IsEmpty(lstartpos) Then .Move lstartpos
End With
'n.b. error with cfr in excel 97
If Val(Application.Version) < 9 Then
With rsName
.MoveFirst
lRowPos = 2
Do While Not .EOF
For nField = 1 To .Fields.Count
Cells(lRowPos, nField).Value = rsName(nField - 1).Value
Next nField
.MoveNext
lRowPos = lRowPos + 1
Loop
End With
Else
#If VBA6 Then
Cells(2, 1).CopyFromRecordset rsName
#End If
End If
On Error GoTo 0
End Sub

Robin Hammond
www.enhanceddatasystems.com