DoCmd.OutputTo in xlsx (Excel 2007+) Format

A common technique for exporting data to Excel is to utilize the  DoCmd.OutputTo Method.

Now, previously (pre Office 2007), the basic format to export a query to Excel format was simply:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLS, , True

BUT, once you port such a database to a post Office 2007 computer, you start to get it exported as a “Microsoft Excel 5.0/95 Workbook (*.xls)” format and such a file will thus open Excel in compatibility mode possibly given the user compatibility errors/messages when they go to save the file after working with it.

So unless you have to ensure legacy compatibility, it is strongly advisable to update your code to export the data in “Excel Workbook (*.xlsx)” format, the new Excel file format thus eliminating any compatibility issues. Hence, we simply need to change the OutputFormat format variable to a more appropriate format (acFormatXLS -> acFormatXLSX). So your code would thus become:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, , True

Alternately, you could also use:

DoCmd.OutputTo acOutputQuery, "YourQueryName", "Excel Workbook (*.xlsx)", , True

Looking to export to a different format?

Nothing could be easier! In the VBA Object browser, go to the Access Contants library and look over the various acFormat**** available to you. Such as:

  • acFormatHTML
  • acFormatPDF
  • acFormatRTF
  • acFormatSNP
  • acFormatTXT
  • acFormatXLS
  • acFormatXLSB
  • acFormatXLSX
  • acFormatPS

Taking Things a Little Further

It wouldn’t be very difficult to create a custom DoCmd.OutputTo function which could determine the version of MS Excel installed and the utilize the more appropriate OutputFormat variable. Then you code would be 100% compatible regardless of what your users have installed!

Are There Any Alternatives

Another approach available to us is simply automate Excel. This is the approach I have adopted. In my early development days, I used the built-in DoCmd.OutputTo command, but have long since abandoned it for my own custom export function: Export2XLS. Why? The custom function approach is fully compatible with whichever version of Excel a user has installed, and gives the programmer far more control over what the final export looks like. You can format Cells/Ranges, apply colors, fonts, …

10 responses on “DoCmd.OutputTo in xlsx (Excel 2007+) Format”

  1. Priscilla

    Thanks for the article – It helped me solve an issue while changing a file from from xls to xlsx.

  2. Joseph

    Thank You.

    I think the problem is that Access is so old that the main function floating around is “TransferSpreadsheet”, when in fact “OutputTo” is far more advanced.

  3. Rich Flanigan

    Hello, I have a ribbon call back that exports my report to xls. A while back we went from Access 2007 to 2010. In the past year it was noticed while running Access on a Citrix server as a published app – run this report choosing the export to xls option that the first time you click it it launches a ghost window and then closes with no xls. Click the call back again and the report generates the xls correctly. When running on a desktop instead of published app it seems to work correctly.
    I am trying to determine if the DoCmd.OutputTo acOutputReport, RptName, “MicrosoftExcelBiff8(*.xls)”, “”, True, “”, 0 might have changed between 2007 and 2010.
    A couple of knowns – Citrix environment hasn’t changed. We are running 2007 Runtime on each server.
    Thanks for any insight.

    1. Daniel Pineault Post author

      To my knowledge, nothing changed with the DoCmd.OutputTo command, but I, myself, stopped using it years ago because of its limitations and moved onto using Excel Automation, see: MS Access – VBA – Export Records to Excel and haven’t looked back since.

      I know I have issues with orphan windows launching and requiring to issue the command multiple times from published apps with a couple of my clients. So I’d be leaning towards a CITRIX issue, but this is just my gut talking now.

  5. Jyoti sharma

    What will be the command for exporting table/query from same command. I have a button to export whether the user select table / query, user will be allowed to export to excel. But I am not able to use the same command for both. Please help.

  6. Doug Trosper

    When I use either of the following formats I get a corrupted .xls file:

    DoCmd.OutputTo acOutputQuery, “YourQueryName”, acFormatXLSX, , True
    DoCmd.OutputTo acOutputQuery, “YourQueryName”, “Excel Workbook (*.xlsx)”, , True

    the only format that works for me is acFormatXLS so far…

    Doug

  7. Doug Trosper

    Never mind last post. I was saving as .xls file, my bad! Changed to .xlsx and it works. However I do get less satisfactory row formatting (formats to largest field, while I would rather keep row to one line.
    Doug

  8. Maciek

    Hello,
    I have one question, if you could help me.
    Is it possible to export opened access report to excel to xlsx format? When I open the report, right click on it, and choose export – excel, I can see that only xls format is possible but xlsx option is missing.
    For tables xlsx format is possible, but in case of report I don’t see such an option.