MS Access output to Excel

Posted on 2014-08-13
Last Modified: 2014-08-13

I currently use the following statement to output tables in Access to Excel:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AccessTablexx", outputFileName, True

I now have a new Access table but I only wish to output rows from the new table where a field value is > 0.  So if there are 100 rows in the table and only 6 rows have a value > 0 for a field I wish to output only those 6 rows to Excel.

As an alternative I could use VBA to remove rows in the Excel worksheet where the field value is 0 but I would prefer to filter the output from Access if that was possible.

Many thanks
Question by:alisonthom
    LVL 47

    Accepted Solution


    Instead of a table you could create a query with your specifications and export it

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "yourAccessQuery", outputFileName, True


    Author Closing Comment

    Yes, that would also be fine.  Thank you!

    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

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now