Saturday, October 17, 2009

Export filtered Access data to Excel

In my free time I've been creating an MSAccess database containing a few data-entry forms. One of these forms allows the user to filter records based on several different criteria. This part was relatively straightforward. The difficulty was in trying to export the filtered information to an Excel spreadsheet. Although this functionality exists in Access, the installed help file was less than helpful. Forum posts seemed to contain partial solutions or solve something almost, but not quite what I was trying to do.

The following VBA subroutine is the eventual solution:

Private Sub Export_Click()
    Dim whereClause As String
    
    ' Generate our WHERE clause based on form values
    whereClause = GenerateFilterClause
    
    ' If we have no filter, export nothing
    If IsEmptyString(Nz(whereClause)) Then
        Exit Sub
    End If
    
    Dim query As String
    query = "SELECT DISTINCTROW Contacts.* " & _
            " FROM Contacts " & _
            " INNER JOIN Applications " & _
            " ON Contacts.ContactID = Applications.ContactID " & _
            " WHERE " & whereClause & ";"
    
    Dim filename As String
    filename = "c:\test.xls"

    ' Placeholder query already in the database
    Dim queryName As String
    queryName = "FilterExportQuery"

    ' Update the placeholder with the created query
    CurrentDb.QueryDefs(queryName).SQL = query

    ' Run the export
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, queryName, filename
End Sub

3 comments:

  1. Brilliant ¡¡¡ , it was really helpful

    Thanks a lot

    ReplyDelete
  2. I am not very familiar with VBA. Could you go step by step as to how I would enter that into a Macro after you open up access and open up the table?

    Thank you!
    Walt

    ReplyDelete
  3. @Walt - I'm not familiar with setting up a macro on the table itself. This example was tied to a button click on an Excel form I created. You might find this page useful.

    ReplyDelete

Note: Only a member of this blog may post a comment.