How to Save an Excel Macro-Enabled Template as an XLSM Workbook on Mac
Question details
The user needs to save an Excel macro-enabled template or existing file as an XLSM workbook on Mac using VBA without losing macros.
- Product
- Excel
- Device & OS
- Mac
- Scenario
- Using the VBA SaveAs method to convert or save a macro-enabled file on a Mac.
- Observed behavior
- If the correct FileFormat value is not specified during the SaveAs process, Excel defaults to saving a macro-free workbook, causing the VBA project to be stripped or triggering an error.
Ensure your Excel Developer tab is enabled and that you have securely backed up your original macro-enabled template before testing new VBA scripts.
Specify the Correct FileFormat for XLSM Workbooks in VBA
Use the appropriate FileFormat enumeration in your VBA script to ensure the workbook retains its macros when saved on a Mac.
When saving a macro-enabled file via VBA on a Mac, you must explicitly declare the file format in your code. For a macro-enabled workbook (.xlsm), the required FileFormat value is 52. Omitting this parameter often defaults the save process to a standard macro-free format (.xlsx), which strips your code.
Press 'Option + F11' on your Mac keyboard or navigate to the Developer tab and click 'Visual Basic' to launch the editor.
Find the module or macro containing the SaveAs method that you are using to save your template.
Update your code to include the FileFormat parameter explicitly. Modify your script to look like this: ActiveWorkbook.SaveAs Filename:="YourFilePath.xlsm", FileFormat:=52
Execute the macro and navigate to your saved location. Open the newly created .xlsm file to confirm the macros are preserved and functioning.
Experience Seamless Spreadsheet Management with WPS Office
If you are encountering frustrating formatting or macro errors in Microsoft Excel for Mac, consider switching to WPS Office. It provides a lightweight, highly compatible, and user-friendly environment for your daily spreadsheet tasks without the hefty subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free version for Mac.
- 2. Install WPS Office: Follow the simple on-screen instructions to install the application on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel files to enjoy seamless compatibility.
Frequently Asked Questions
What is the FileFormat value for saving an XLSM file in VBA?
In Excel VBA, the correct FileFormat value for a macro-enabled workbook (.xlsm) is 52. Explicitly setting this value ensures that your macros are not stripped out when the file is saved.
Why did my macro disappear after saving the template on Mac?
If you saved the template without explicitly specifying a macro-enabled format in your VBA code, Excel defaults to saving it as a standard macro-free workbook (.xlsx). This action permanently removes any contained VBA code.
Can I save a macro-enabled template (.xltm) as a standard workbook?
Yes, you can. However, if you save it as a standard workbook (.xlsx, using FileFormat 51), any VBA macros contained in the template will be permanently removed. To retain the macros, it must be saved as an .xlsm file.
