Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset - Is there a way to filter data that's copied?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Sample for Oracle-to-Excel extraction with CopyFromRecordset | Excel Discussion (Misc queries) | |||
CopyFromRecordset Problem | Excel Discussion (Misc queries) | |||
Format data in Excel after using copyfromrecordset | Excel Discussion (Misc queries) | |||
CopyFromRecordset only pastes 1823 characters in a cell!? | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |