Excel CopyFromRecordset filtered rst

[Andrzejek]

I am trying to display ADODB recordset in Excel this way:

Private Sub ExcelAllPlanningEventsEnterRecords(intWS As Integer)
Dim i As Integer

With xlApp
    [green]'Place Header row from recordset[/green]
    For i = 0 To recOne.Fields.Count - 1
        .Sheets(intWS).Cells(1, i + 1) = recOne.Fields(i).Name
    Next
    
    [blue]recOne.Filter = " PSLOC_STATUS <> 'N'"[/blue]
    
    .Sheets(intWS).Range("A2").CopyFromRecordset recOne
    
    For i = 1 To recOne.Fields.Count
        .Sheets(intWS).Columns(i).EntireColumn.AutoFit
    Next
    
    .Rows("1:1").Font.Bold = True
    .Range("A2").Select
    .ActiveWindow.FreezePanes = True
End With

End Sub

All works fine except - I can NOT display filtered recOne, this code just ignores my filter (the blue line of code).

How can I display Filtered recOne in Excel using CopyFromRecordset?

Have fun.

---- Andy

 

[Andrzejek]

After digging for some info on the Net, I gave up and instead of using [tt]CopyFromRecordset [/tt] I decided to just do it 'by hand', the long way.

Some places on the Net indicated that Excel can not use Filtered ADO recordset with CopyFromRecordset :-(

Have fun.

---- Andy

 

[SkipVought]

In Excel VBA, when I step thru the code, it goes south on the .Filter statement.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWise]

Why dont you open the recordeset with the filter

 

[Andrzejek]

Skip,
the filter in the code is just ignored. The code works fine, it does not crash for me. If the full recordset shows 100 records, after filter is on it shows 50 records, yet in Excel there are still 100 records after CopyFromRecordset statement.

pwise,
The whole idea of Filter is that you can have one recordset, and if you need a subset of the data - you apply the filter. I could use another recordset, but I wanted to have with just one.

Anyway, no matter what you do to the original recordset, filter or not, CopyFromRecordset will bring teh whole data anyways.

Have fun.

---- Andy

 

[HarleyQuinn]

Andy,

What other PSLOC_STATUS values can there be?

Cheers

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Andrzejek]

[tt]
N - New
A - Active
H - On Hold
L - Awarderd
F - Finished
C - Completed
Z - Archived
etc.
[/tt]

Have fun.

---- Andy

 

[strongm]

>Excel can not use Filtered ADO recordset with CopyFromRecordset

Oh, it's more fundamental than that; the recordset's own CLone method ignores the filter.

What you need is my CloneFilteredRecordset function ...:

[blue]
Private Function CloneFilteredRecordset(rs As Recordset) As Recordset
    Dim myTempStream As Stream
    
    Set myTempStream = New Stream
    rs.Save myTempStream, adPersistXML
    Set CloneFilteredRecordset = New Recordset
    CloneFilteredRecordset.Open myTempStream
End Function[/blue]

which might be called in your code as:

[blue].Sheets(intWS).Range("A2").CopyFromRecordset CloneFilteredRecordset(recOne)[/blue]

 

[Andrzejek]

I did try to Clone the recordset, but not like you.

I may try your suggestion in some other time.

Could you explain a little about [tt]myTempStream [/tt]. please? I am not familiar with Streams

Have fun.

---- Andy

 

[strongm]

Basically is a FIFO queue stored in memory. My method squirts the the filtered recordset in at one end, and extracts it at the other end.