Filter ADODC records and send to Excel File

Posted on 2004-11-15
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 69

    Expert Comment

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

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

    Author Comment

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

    LVL 23

    Accepted Solution

    Before this part of code

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


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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now