ExcelBanter - CopyFromRecordset - Is there a way to filter data that's copied?

Hi Mark,

You can use the Filter method of the Recordset object to do this prior
to dumping it onto the worksheet using the CopyFromRecordset method. Based
on your example this might look like the following:

Dim rst As ADODB.Recordset
Dim xlWs As Excel.Worksheet
Dim iCol As Long
Dim szSale As String
Dim szLot As String

''' Load the recordset.
Set rst = New ADODB.Recordset
''' etc....

''' Get the filter criteria:
szSale = Sheet1.Range("A1").Value
szLot = Sheet1.Range("B1").Value

''' Filter the recordset.
rst.Filter = "SALE = '" & szSale & "' AND LOT = '" & szLot & "'"

''' Dump the data onto the worksheet.
For iCol = 1 To rst.Fields.Count
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Mark" wrote in message
om...
Here's the code:

For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst

I'd like to limit the data copied based on the contents of field 1 and
2 (which are named SALE and LOT in my source MS-Access query "rst").

I have a cell in the spreadsheet with the desired SALE and LOT. I
read these two text strings into the VB code, but don't know the
syntax to limit the CopyFromRecordset method (if it's even possible).

Thanks, Mark