How to Save an Excel Workbook with Access VBA Using SaveAs
Question details
The user needs to prompt a Save As dialog from Access VBA and successfully save an Excel macro-enabled workbook (.xlsm) with the correct file extension and format.
- Product
- Excel / Access VBA
- Device & OS
- not provided
- Scenario
- Automating Excel tasks from Microsoft Access via VBA and allowing the user to choose the save destination.
- Observed behavior
- The GetSaveAsFileName method only displays a Save As dialog and returns the selected path without actually saving the workbook or applying the correct file format.
Ensure that your Microsoft Access database has the Microsoft Excel Object Library enabled in the VBA editor's References to allow smooth cross-application automation.
Use GetSaveAsFileName Followed by ActiveWorkbook.SaveAs
Prompt the user for a file location using GetSaveAsFileName, check if they canceled, and then execute the actual save command using the SaveAs method with the correct file format code.
The GetSaveAsFileName method simply brings up the dialog box and returns the file path string selected by the user. It does not perform any saving actions. To save the file in a specific format like a Macro-Enabled Workbook (.xlsm), you must use the ActiveWorkbook.SaveAs method and define FileFormat:=52.
In your Access VBA editor, locate the subroutine controlling your Excel application object (e.g., XLapp) and declare a variable to store the path: `Dim strFileName As Variant`.
Trigger the dialog box to get the desired file path using named arguments for clarity: `strFileName = XLapp.GetSaveAsFileName(InitialFileName:=path, FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")`.
Add an If statement to verify the user did not click Cancel. Since GetSaveAsFileName returns a Boolean False on cancellation, use: `If strFileName <> False Then`.
Inside the If block, invoke the actual save action using the path returned and specifying the macro-enabled format (52): `XLapp.ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=52`.
Try WPS Office for Seamless Macro and Spreadsheet Management
While Microsoft Access and Excel offer specific VBA integrations, WPS Office provides a lightweight, highly compatible alternative for handling your complex spreadsheets. WPS Spreadsheet fully supports VBA macros, allowing you to run and edit your existing .xlsm files effortlessly.
- 1. Visit WPS Office: Go to the official WPS Office website on your browser.
- 2. Download the Installer: Click the Free Download button to get the latest installation package.
- 3. Install and Enable VBA: Run the installer, launch WPS Spreadsheet, and open your .xlsm files directly to continue your macro work.
Frequently Asked Questions
What does FileFormat:=52 mean in Excel VBA?
In Excel VBA, FileFormat:=52 is the enumeration value for xlOpenXMLWorkbookMacroEnabled. It instructs the application to save the workbook specifically as an Excel Macro-Enabled Workbook with an .xlsm extension.
Why does GetSaveAsFileName create a file without an extension?
The GetSaveAsFileName method only returns a string representing the user's choice; it doesn't process file saving. If you omit the file type extension in your subsequent SaveAs command or don't explicitly declare the FileFormat, Excel will save the file based on default settings, often leading to missing or incorrect extensions.
How do I stop VBA from crashing if the user clicks Cancel in the Save As dialog?
When a user clicks Cancel, GetSaveAsFileName returns the Boolean value False instead of a string. To prevent crashes, capture the return value in a Variant variable and check `If YourVariable <> False Then` before running the SaveAs command.
