Access VBA DoCmd.OutputTo Method

This Access tutorial explains how to use DoCmd.OutputTo Method and DoCmd.TransferSpreadSheet Method to export different objects to specific file types, for example, export Query to xlsx.

You may also want to read:

Access VBA auto generate mass report by group to Excel

Access VBA DoCmd.TransferSpreadSheet Method

Access Export all Queries to Excel

Syntax of DoCmd.OutputTo Method

DoCmd.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding, OutputQuality)
Name Required/Optional Description
ObjectType Required Type of object to export

Name Value Description
acOutputForm 2 Form
acOutputFunction 10 User-Defined Function
acOutputModule 5 Module
acOutputQuery 1 Query
acOutputReport 3 Report
acOutputServerView 7 Server View
acOutputStoredProcedure 9 Stored Procedure
acOutputTable 0 Table
ObjectName Required The Name of the Object to export, such as Query Name, Report Name.
OutputFormat Required The output format

OutputFile Required A string expression that’s the full name, including the path, of the file you want to output the object to.For example, “C:\test\test.xlsx”
AutoStart Optional Type True to open the exported file, False to disable opening. Default is False.
TemplateFile Optional A string expression that’s the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.
Encoding Optional The type of character encoding format you want used to output the text or HTML data.
OutputQuality Optional The type of output device to optimize for. The default value is acExportQualityPrint.

Name Value Description
acExportQualityPrint 0 The output is optimized for printing.
acExportQualityScreen 1 The output is optimized for onscreen display.

Example – export Query to xlsx (DoCmd.OutputTo acOutputQuery)

The below example export “Query1” to C:\test\test.xlsx.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLSX, Outputfile:="C:\test\test.xlsx"

You may also use DoCmd.TransferSpreadsheet Method, the syntax is slightly different.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", "C:\test\test", True

DoCmd.TransferSpreadsheet vs DoCmd.OutputTo

DoCmd.TransferSpreadsheet Method has other functions other than exporting, such as import files and exporting to an Excel template. In terms of exporting Query to XLSX, Transferspreadsheet is quicker and the file size is smaller because it does not contain any Query formatting, and error handling is different as well.

For exporting Query to Excel, I personally prefer OutputTo Method because I can adjust the column width in Query before export.

But there is one problem with DoCmd.OutputTo Method. If you export the Query to XLSX format. Some Access formats cannot be perfectly supported, and an error pops up when users try to open the exported workbook, and users have to save the file as a new one for auto repair. To work around this issue, you can export as XLS format instead.

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Query1", OutputFormat:=acFormatXLS, Outputfile:="C:\test\test.xls"

Outbound References

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

3 thoughts on “Access VBA DoCmd.OutputTo Method

  1. Great info. Do any of these export type commands let me export to an excel template file? I have an excel template with a custom ribbon with buttons for preset sorts and filters. However, all of these assume creation of a new file. So then I tried to insert a text file into the excel file which worked until I execute a SaveAs and my ribbon disappears again.

Leave a Reply

Your email address will not be published. Required fields are marked *