Export Access Data to Separate Excel Workbooks by Facility
Question details
Export Access records into separate Excel workbooks for each facility, with a choice of destination folder and file names.

- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Access query contains a FacilityName field, and the user wants one workbook per facility.
- Observed behavior
- Each workbook should contain only the records for its corresponding facility.
Confirm that your source query returns a text field named FacilityName and note the query's exact name. Enable VBA content for the database and make sure you have permission to create files in the destination folder.
Use Access VBA to export one workbook per facility
Loop through distinct facility names, filter the source query for each one, and choose an output folder and file name for each workbook.
The example exports non-null FacilityName values to .xlsx files. It creates a temporary saved query for the export and deletes it afterward; it does not apply an Excel template or preserve template formatting.
In Access, press Alt+F11, then choose Insert > Module. Replace YourSourceQuery in the following code with the name of your saved query.
Paste this code into the module: Public Sub ExportFacilities() Const sourceQuery As String = "YourSourceQuery" Const tempQuery As String = "zTempFacilityExport" Dim db As DAO.Database Dim rs As DAO.Recordset Dim q As DAO.QueryDef Dim folder As String, facility As String, fileName As String, outFile As String Dim created As Boolean On Error GoTo HandleError Set db = CurrentDb For Each q In db.QueryDefs If q.Name = tempQuery Then MsgBox "Rename or remove the existing query " & tempQuery & " before running this export." Exit Sub End If Next q With Application.FileDialog(4) .Title = "Choose the export folder" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub folder = .SelectedItems(1) End With Set rs = db.OpenRecordset("SELECT DISTINCT [FacilityName] FROM [" & sourceQuery & "] WHERE [FacilityName] Is Not Null ORDER BY [FacilityName]", dbOpenSnapshot) Set q = db.CreateQueryDef(tempQuery, "SELECT * FROM [" & sourceQuery & "] WHERE 1=0") created = True Do While Not rs.EOF facility = CStr(rs!FacilityName) fileName = InputBox("File name for " & facility & " (without .xlsx):", "Export facility", "Facility_" & SafeFileName(facility)) If Len(fileName) = 0 Then GoTo CleanUp If fileName <> SafeFileName(fileName) Or Right$(fileName, 1) = "." Then Err.Raise 5, , "Invalid file name: " & fileName outFile = folder & "\" & fileName & ".xlsx" If Len(Dir(outFile)) > 0 Then Err.Raise 5, , "File already exists: " & outFile q.SQL = "SELECT * FROM [" & sourceQuery & "] WHERE [FacilityName] = '" & Replace(facility, "'", "''") & "'" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tempQuery, outFile, True rs.MoveNext Loop MsgBox "Export complete." CleanUp: On Error Resume Next If Not rs Is Nothing Then rs.Close Set q = Nothing If created Then db.QueryDefs.Delete tempQuery Exit Sub HandleError: MsgBox "Export stopped: " & Err.Description Resume CleanUp End Sub Private Function SafeFileName(ByVal value As String) As String Dim character As Variant value = Trim$(value) For Each character In Array("\", "/", ":", "*", "?", Chr$(34), "<", ">", "|") value = Replace(value, character, "_") Next character SafeFileName = value End Function
Place the cursor inside ExportFacilities and press F5. Select a folder, then accept or edit the suggested file name for each facility. Add a week number to a name if needed; leave the name blank to stop.
Open the selected folder and inspect several .xlsx files in Excel. Confirm that each contains the expected facility's records and column headers.

Review exported facility workbooks in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar interface for opening and reviewing exported Excel workbooks. Its Excel format compatibility helps you move existing spreadsheets into your workflow, though the Access VBA export must be run in Access.

Frequently Asked Questions
How do I include a week number in each workbook name?
When the export prompts for a file name, add the week number, such as Facility_North_Week_12. Enter the name without the .xlsx extension.
What happens if a facility name contains an apostrophe?
The VBA procedure doubles apostrophes when building the WHERE clause, so a text value such as O'Brien can be matched correctly.
Can I export into a formatted Excel template?
The sample creates new workbooks and does not use a template. To preserve a template's formatting or write to a sheet named Raw Data, use a separate Excel automation routine that opens a copy of the template and writes the filtered data to that sheet.
Why did the export stop before processing every facility?
The procedure stops if a file name is invalid, a workbook with that name already exists, or an export error occurs. Check the message and destination folder, then change the conflicting name or resolve the reported error before running it again.




