Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Ignores Filters
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? -- Ron Mittelman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset Ignores Filters
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 "Ron Mittelman" wrote in message ... 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? -- Ron Mittelman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table filters, especially DATE filters | Excel Worksheet Functions | |||
Filters, Subtotal & Intacted Results after the filters' Removal | Excel Discussion (Misc queries) | |||
CopyFromRecordset does nothing | Excel Programming | |||
CopyFromRecordset does nothing | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |