Data Import & Export

Export Access Query Results to Separate Excel Worksheets

Adam DavisAdam Davis Nov 18, 2026 870 views

Question details

Export customer query results from Microsoft Access into a separate Excel worksheet for each store.

Export Access Query Results to Separate Excel Worksheets
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
A user wants to combine store-specific customer query results in one Excel workbook, with each store on its own worksheet.
Observed behavior
An attempted export references a report and returns run-time error 3011 instead of producing the requested worksheets.
Before you start

In Access, confirm that each store has a saved query that opens successfully and returns the intended records. The VBA method below requires desktop Excel, permission to run VBA, and an existing destination folder.

Solution 1Recommended

Create one Excel worksheet per Access query with VBA

Use Excel automation when you need to name each store worksheet in a new workbook.

TransferSpreadsheet exports tables and queries, not reports. Its Range argument must be left blank when exporting, so it cannot reliably be used to assign an export to a named worksheet.

This example creates a new workbook and writes query results starting at A1, with field names in the first row. It does not add sheets to an existing workbook.

1
Prepare the store queries

In the Access Navigation Pane, open each saved query and verify its results. Replace qryCustSmith and qryCustJones in the example with your query names; use separate filters so each query returns the appropriate store's records.

2
Add the export procedure

Press Alt+F11 in Access, select Insert > Module, and paste the following code: Sub ExportStoreQueries() Dim xl As Object, wb As Object, ws As Object, rs As Object Dim queries As Variant, sheets As Variant Dim outputPath As String Dim i As Long, j As Long outputPath = "C:\MyDatafiles\Customers.xlsx" If Len(Dir$(outputPath)) > 0 Then MsgBox "Choose a new output file name; the file already exists." Exit Sub End If queries = Array("qryCustSmith", "qryCustJones") sheets = Array("Smith", "Jones") On Error GoTo Failed Set xl = CreateObject("Excel.Application") Set wb = xl.Workbooks.Add For i = LBound(queries) To UBound(queries) If i = LBound(queries) Then Set ws = wb.Worksheets(1) Else Set ws = wb.Worksheets.Add(, wb.Worksheets(wb.Worksheets.Count)) End If ws.Name = sheets(i) Set rs = CurrentDb.OpenRecordset(queries(i)) For j = 0 To rs.Fields.Count - 1 ws.Cells(1, j + 1).Value = rs.Fields(j).Name Next j ws.Range("A2").CopyFromRecordset rs rs.Close Set rs = Nothing Next i wb.SaveAs outputPath, 51 wb.Close False xl.Quit MsgBox "Export complete: " & outputPath Exit Sub Failed: MsgBox "Export failed: " & Err.Description On Error Resume Next If Not rs Is Nothing Then rs.Close If Not wb Is Nothing Then wb.Close False If Not xl Is Nothing Then xl.Quit End Sub

3
Set the destination and run

Edit outputPath to a new .xlsx file in an existing folder. Update both arrays with matching query and worksheet names, place the cursor inside ExportStoreQueries, and press F5.

4
Check the workbook

Open the resulting Customers.xlsx file in Excel and select each store tab to verify its headers and records. A new workbook may also contain unused default worksheets, which you can delete.

Create one Excel worksheet per Access query with VBA
Query and sheet names: Use valid, unique Excel worksheet names of no more than 31 characters. Queries requiring unresolved parameters or form controls may need adjustment before they can be opened by the procedure.
Free Microsoft Office alternative

Review exported store worksheets in WPS Office

WPS Office is a free, lightweight, easy-to-use Office alternative with a familiar spreadsheet interface. Open your exported Excel workbook in WPS Spreadsheets to review store tabs and migrate existing files easily; it does not fix Access export errors.

Review customer data on separate store worksheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceMove existing Office files to a lightweight suite
microsoft office alternative - wps office

Frequently Asked Questions

Can TransferSpreadsheet name the destination worksheet?

No. The Range argument must be left blank for an export. To control worksheet names, use Excel automation or another method that creates and names sheets after exporting.

Why does exporting a report give me error 3011?

TransferSpreadsheet expects a table or query, not a report. Check the name in the Access Navigation Pane, then inspect the report's Record Source in Design View and export the underlying query instead.

How can I export only one store's customers?

Open the saved query in Access Query Design, add a criterion for the store field, and run it to verify the results. Save a separate query for each store or otherwise supply the appropriate filter before exporting.

Does the VBA example add worksheets to an existing workbook?

No. It creates a new workbook and stops if the output file already exists. Change outputPath to a new file name before running the procedure.