This tutorial will cover the ways to import data from Excel into an Access Table and ways to export Access objects (Queries, Reports, Tables, or Forms) to Excel.

Import Excel File Into Access

To import an Excel file to Access, use the acImport option of DoCmd.TransferSpreadsheet :

Or you can use DoCmd.TransferText to import a CSV file:

Import Excel to Access Function

This function can be used to import an Excel file or CSV file into an Access Table:

You can call the function like this:

Access VBA Export to New Excel File

To export an Access object to a new Excel file, use the DoCmd.OutputTo method or the DoCmd.TransferSpreadsheet method:

Export Query to Excel

This line of VBA code will export a Query to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Note: This code exports to XLSX format. Instead you can update the arguments to export to a CSV or XLS file format instead (ex. acFormatXLSX to acFormatXLS).

Export Report to Excel

This line of code will export a Report to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Export Table to Excel

This line of code will export a Table to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Export Form to Excel

This line of code will export a Form to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Export to Excel Functions

These one line commands work great to export to a new Excel file. However, they will not be able to export into an existing workbook.  In the section below we introduce functions that allow you to append your export to an existing Excel file.

Below that, we’ve included some additional functions to export to new Excel files, including error handling and more.

Export to Existing Excel File

The above code examples work great to export Access objects to a new Excel file.  However, they will not be able to export into an existing workbook.

To export Access objects to an existing Excel workbook we’ve created the following function:

You can use the function like this:

Notice you are asked to define:

  • What to Output? Table, Report, Query, or Form
  • Object Name
  • Output Sheet Name
  • Output File Path and Name.

Export SQL Query to Excel

Instead you can export an SQL query to Excel using a similar function:

Called like this:

Where you are asked to input:

  • SQL Query
  • Output Sheet Name
  • Output File Path and Name.

Function to Export to New Excel File

These functions allow you to export Access objects to a new Excel workbook. You might find them more useful than the simple single lines at the top of the document.

The function can be called like this: