Filter ADODC records and send to Excel File

Posted on 2004-11-15
Medium Priority
Last Modified: 2013-12-25
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.

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
' 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"
' 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
    Screen.MousePointer = vbDefault
End Sub

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

Question by:fanguru1
LVL 70

Expert Comment

by:Éric Moreau
ID: 12585919
CopyFromRecordset grabs all rows (filtered or not) like you just discovered.

The only way is to copy cell-by-cell.

Author Comment

ID: 12586972
I'm fairly new to this so any help with that code would be appreciated.

LVL 23

Accepted Solution

Ioannis Paraskevopoulos earned 2000 total points
ID: 12592443
Before this part of code

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


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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question