Export Access Query Results to Separate Excel Worksheets
Question details
Export customer query results from Microsoft Access into a separate Excel worksheet for each store.

- 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.
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.
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.
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.
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
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.
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.

Resolve error 3011 before exporting an Access query
Use this check when Access cannot find the object named in an export command.
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.

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.




