Filter ADODC records and send to Excel File

I have a project that includes a form 'form1' for data input only and another form 'form2' for viewing the data in a datagrid. I use a combo box that automatically populates with item numbers that I would like to filter for. I can send the data to excel for formatting and printing but I can only send all the data.
How can incorporate my recordset.filter so that I only send the filtered data. Maybe I can use two command buttons to do this. Command1 just sends all the data from the grid and Command2 will send just the filtered data.
Below is the code I am using for connection to Acces and Excel and the code for my Filtering.

'CONNECTION
Private Sub Command2_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim excel_app As excel.Application
Dim excel_sheet As excel.Worksheet
    Screen.MousePointer = vbHourglass
    DoEvents
' Open the Access database.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "c:\my documents\programming\bs tree sales\treelotsales1.mdb"
        conn.Open
' Select the Access data.
    Set rs = conn.Execute("Sales")
' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
    excel_app.Visible = True
' Open the Excel workbook.
    excel_app.Workbooks.Open "c:\my documents\programming\bs tree sales\test2.xls"
' Use the Recordset to fill the table.
    Set excel_sheet = excel_app.ActiveSheet
        excel_sheet.Range("A3").CopyFromRecordset rs
    rs.Close
    conn.Close
    Screen.MousePointer = vbDefault
End Sub

'FILTER
item1 = Form2.Combo1.Text
Form2.Adodc1.Recordset.filter = "ITEM like '" & item1 & "'"

Thanks,
fanguru1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
CopyFromRecordset grabs all rows (filtered or not) like you just discovered.

The only way is to copy cell-by-cell.
0
fanguru1Author Commented:
I'm fairly new to this so any help with that code would be appreciated.

thanks,
0
Ioannis ParaskevopoulosCommented:
Before this part of code

    Set excel_sheet = excel_app.ActiveSheet
        excel_sheet.Range("A3").CopyFromRecordset rs
    rs.Close

write:


rs.filter="ITEM like '" & item1 & "'"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.