CopyFromRecordset with Filtered ADO recordset

[Sheldon Penner]

My Excel application contains the following code to enter data from an ADO
recordset, rsStats, onto an Excel spreadsheet:

rsStats.Filter = "[DIST_CODE]='" & strTerr & "'"
RecCount = rsStats.RecordCount
..Cells(n, startCol).CopyFromRecordset rsStats

This code is contained within a loop that assigns different values to
strTerr and n. The result is that it returns the various filtered recordsets
at different locations on the spreadsheet.

This works fine in VBA, but now I have to transfer this functionality into a
vbscript that uses Office Automation to instantiate the Excel application,
open the Excel file and insert the data. When the code is run from vbscript,
rsStats.RecordCount returns the correct number of records for each filtering
of the recordset, but CopyFromRecordset always prints the complete,
unfiltered recordset.

Is there some way I can get CopyFromRecordset to recognize the filter when
working through Office Automation?

 

[Tim Williams]

I can't think of any reason why this wouldn't work the same in vbscript as
it does in VBA, as long as you didn't miss something in the translation.
Have you defined all ADO/XL intrinsic constants or replaced them with their
values ?

Tim

 

[Sheldon Penner]

I did replace all constants with their values.

The issue has become academic in this instance. I was unaware of the
existence of the Subtotal method, so my code had been copying filtered
portions of the recordset, then adding a subtotals row beneath it, then
copying the next filtered portion, and so on. As soon as I learned of the
Subtotal method, I rewrote my code to simply copy the entire sorted recordset
onto the spreadsheet, then apply the method to add subtotals and a grand
total row. Simple!

Which leaves the question of CopyFromRecordset and filtered recordsets
unanswered. It does appear to me that Excel deals differently with it from
internal vba modules than from external vbscript.

I can't think of any reason why this wouldn't work the same in vbscript as
it does in VBA, as long as you didn't miss something in the translation.
Have you defined all ADO/XL intrinsic constants or replaced them with their
values ?

Tim

My Excel application contains the following code to enter data from an ADO
recordset, rsStats, onto an Excel spreadsheet:

rsStats.Filter = "[DIST_CODE]='" & strTerr & "'"
RecCount = rsStats.RecordCount
.Cells(n, startCol).CopyFromRecordset rsStats

This code is contained within a loop that assigns different values to
strTerr and n. The result is that it returns the various filtered
recordsets
at different locations on the spreadsheet.

This works fine in VBA, but now I have to transfer this functionality into
a
vbscript that uses Office Automation to instantiate the Excel application,
open the Excel file and insert the data. When the code is run from
vbscript,
rsStats.RecordCount returns the correct number of records for each
filtering
of the recordset, but CopyFromRecordset always prints the complete,
unfiltered recordset.

Is there some way I can get CopyFromRecordset to recognize the filter when
working through Office Automation?