How to Export Data from Microsoft Access to Excel

You can export data from a Microsoft Access database to a file format that Microsoft Excel can read by using the Export Wizard.

How to Export Data from Microsoft Access to Excel

A simple Export Wizard is available in Microsoft Access and may be used to export tables, queries, forms, or reports to Microsoft Excel. By following a precise procedure, exporting data from Access to Excel is easier and quicker than you would expect.

  • Step 1: Select the Object to Export

    Select the object you want to export in the “Navigation Pane” of the source database in Microsoft Access. Don’t export the entire set of data. Then, open the object in the “Datasheet” view and choose the records you want to export, whether it is a table, query, or form, and you only need to export a portion of the data. To do so, double-click the form to open it, right-click the form, and select the “Datasheet View” option.

    note-iconNote

    If the option for Datasheet View is not available, select Design View instead, then press F4 to bring up the Property Sheet task window. Next, choose Form from the drop-down menu at the Property Sheet’s top part. Then, set the Allow Datasheet View property to Yes on the Property Sheet’s Format tab. Finally, select Datasheet View under the Views group on the Design tab.

  • Step 2: Go to External Data Tab

    Go to the “External Data” tab of the Microsoft Access ribbon and click “Excel” under the “Export” group. Review the Excel workbook’s suggested file name in the “Export-Excel Spreadsheet” dialog box (Microsoft Access uses the name of the source object). You can change the file name if you’d like. After that, choose the desired file format from the list in the “File Format” box. Then, select “Export Data with Formatting and Layout” if you wish to export formatted data when exporting a table or a query.

    note-iconNote

    The “Export data with formatting and layout” option is always selected but unavailable (appears dimmed) if you are exporting a form or report.

  • Step 3: View the Destination Excel Workbook

    Select the “Open the Destination File After the Export Operation is Complete” check box to view the destination Excel workbook after the export process is finished. You can check the “Export Only the Selected Records” checkbox if the source object is open and you selected one or more records in the view before initiating the export procedure. Leave this checkbox unchecked if you want to export every record that is shown in the view. After that, click “OK.”

FAQs

What happens when I choose to export data to Excel?

When you choose to export data to Excel, Microsoft Access makes a copy of the chosen data and stores it in a file that Excel can open.

Can I export a portion of a report?

A report cannot be exported in portions, but you can select or open the table or query that the report is based on, and then export a portion of the data in that object.

What happens if an export operation fails due to an error?

If the export operation fails because of an error, Microsoft Access displays a message that describes the cause of the error.