Export an Access Query to Excel with TransferSpreadsheet
Question details
The user needs to provide a destination filename when exporting Access data to Excel with DoCmd.TransferSpreadsheet.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Export an Access query to an Excel workbook using VBA.
- Observed behavior
- The export requires a destination filename and path rather than omitting the FileName argument.
Confirm that the query exists and that the Access database is saved in a folder where you can write files. Choose an XLSX filename and check whether a file with that name already exists.
Provide a Full XLSX Path to TransferSpreadsheet
Use a complete output path and an XLSX spreadsheet type to export a saved Access query.
In Access, press Alt+F11, then choose Insert > Module. Replace "YourQueryName" in the example with the name of your saved query.
Build the filename from the saved database folder and the current date: strFullName = Application.CurrentProject.Path & "\" & Format(Date, "yyyy-mm-dd") & ".xlsx".
In the module, run this procedure: Sub ExportQuery() Dim strFullName As String strFullName = Application.CurrentProject.Path & "\" & Format(Date, "yyyy-mm-dd") & ".xlsx" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourQueryName", strFullName, True End Sub
If Excel is installed, add these lines after TransferSpreadsheet to open the result: Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open strFullName

Open Exported Excel Workbooks in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface. Open an exported XLSX workbook in WPS Spreadsheets to review and edit it without changing your existing Excel file workflow. WPS does not fix or run Access TransferSpreadsheet code.

Frequently Asked Questions
Why does TransferSpreadsheet need a filename for an export?
Access needs a destination for the exported workbook. Supply the FileName argument with a full path, such as C:\Exports\QueryResults.xlsx.
Can I ask the user for the export filename?
Yes. Use VBA's InputBox to request a full output path, check that the response is not empty, and pass it as the FileName argument to DoCmd.TransferSpreadsheet.
How do I export to the same folder as the Access database?
Use Application.CurrentProject.Path to get the saved database's folder, then append a backslash and an XLSX filename before passing the result to TransferSpreadsheet.
Can Access open the exported file automatically?
Yes. If Excel is installed, use CreateObject("Excel.Application"), set its Visible property to True, and call Workbooks.Open with the exported file path.




