none
Automatically export the results of a query into an Excel file RRS feed

  • Question

  • Hi Everyone

    I would like to automatically export the results of a query into an Excel file. I know you can create a table with a query result, but is there an easy way to create an Excel file?

    Paul

    Friday, April 15, 2016 3:29 PM

Answers

  • Hi. Try modifying the macro by changing the following arguments:

    Output File: c:\temp\Cases.xls

    Template File: <leave blank>

    Hope that helps...

    • Marked as answer by Paul-NYS Friday, April 15, 2016 8:41 PM
    Friday, April 15, 2016 7:03 PM

All replies

  • Hi Paul. You can either use the macro action ExportWithFormatting or use the VBA methods OutputTo and TransferSpreadsheet to save a query to an Excel file. For example:

    DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLSX, "C:\FolderName\FileName.xlsx", True

    Hope that helps...

    Friday, April 15, 2016 4:01 PM
  • well to start - when you run the query and are looking at the results; then up in the ribbon select 'External Data' and then in the export area select Excel - and follow the wizard from there.

    that's pretty simple but not necessarily 'automatic' - depending on how one defines it....

    to take automation further you could save that manual export you just did with a name (that option is offered to you as part of the wizard.... and then set up a little code/macro to fire it all upon an appropriate trigger.

    Friday, April 15, 2016 4:05 PM
  • Hi DB Guy

    I created the below export for my CasesExport table. It creates the Excel file, but there is nothing in it. Is there some other parameter or step I am missing?

    Paul

    Friday, April 15, 2016 6:51 PM
  • Hi. Try modifying the macro by changing the following arguments:

    Output File: c:\temp\Cases.xls

    Template File: <leave blank>

    Hope that helps...

    • Marked as answer by Paul-NYS Friday, April 15, 2016 8:41 PM
    Friday, April 15, 2016 7:03 PM
  • Thanks again DB Guy! That did it..

    Paul

    Friday, April 15, 2016 8:41 PM
  • Hi Paul. Glad to hear you got it to work. Good luck with your project.
    Sunday, April 17, 2016 8:06 PM