Is there a method to export an Access Query to individual excel files based on a value in the query RRS feed

  • Question

  • I am looking for a method to use a query to create individual excel files based on a column value in the query. For example, my query contains a field where values are assigned to individual records, and therefore the region will be used many times, e.g. one to many records and I want to break out excel files for each region. No problem when I am dealing with a few regions and can manually export the files. However, when I end up with a large number of regions it becomes too inefficient.

    Any suggestions on an approach?

    Wednesday, February 21, 2018 2:24 AM

All replies

  • Hi,

    If you're using either the TransferSpreadsheet or OutputTo method, there is an argument for the file name. So, you should be able to set it to a value from your query before executing the export.

    Just my 2 cents...

    Wednesday, February 21, 2018 3:26 AM
  • I would probably have a separate query that returns only those regions that would be returned in the original query, with one record for each region.  It's easy enough to do this by grouping by the region or by a SELECT DISTINCT query;  e.g.,

         SELECT DISTINCT Region FROM YourOriginalQuery

    Then I'd open a recordset on this regions query, looping through the records and executing DoCmd.TransferSpreadsheet to export a query that is filtered by the current record's Region, specifying the output file name to include the region as well.

    Filtering by the region is easy if you are running the code from a form, because you can put a hidden text box on the form, push the region into it, and then export a query that selects all the records from the original query where the Region = [Forms]![YourForm]![txtRegionToExport].

    Dirk Goldgar, MS Access MVP
    Access tips:

    Wednesday, February 21, 2018 4:45 AM
  • I used ADO but you can use DAO as well:

    Dim cnn As New ADODB.Connection
    cnn.Open _
       "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\db1 XP.accdb"
    cnn.Execute "SELECT * INTO [Excel 12.0 XML;DATABASE=C:\Test Files\ExcelExport.xlsx;HDR=NO;].[SheetName] FROM [TableName] WHERE COLNAME = 'whatever'"
    Set cnn = Nothing

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 21, 2018 5:43 PM