How to Save an XLSM Workbook as XLSX with the Same Filename via VBA
Question details
The user wants to create an Excel VBA macro that copies the active worksheet into a new workbook and saves it as an XLSX file in the same folder with the identical base filename as the original XLSM file.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the conversion of an XLSM workbook to a macro-free XLSX format without manually retyping the filename or navigating directory structures, typically triggered by clicking a worksheet button.
- Observed behavior
- The user needs the VBA SaveAs method to dynamically inherit the original workbook's directory path and base filename instead of relying on a hardcoded, fixed file path.
Ensure that macros are enabled in your Excel settings and that your original XLSM workbook has been saved at least once so it has a valid directory path.
Automate Saving XLSM as XLSX using a VBA Macro
Use a custom VBA script to dynamically extract the original path and filename, copy the active sheet, and save it as an XLSX file.
When automating file conversions in Excel, VBA allows you to dynamically retrieve the current workbook's path using 'ThisWorkbook.Path' and its name using 'ThisWorkbook.Name'. This ensures your code works universally, even if the file is moved to another folder or shared with colleagues.
Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications window.
Click 'Insert' in the top menu and select 'Module' to create a blank canvas for your code.
Create a Sub procedure. Declare strings for your path and filename. Use 'Replace(ThisWorkbook.Name, ".xlsm", "")' to extract the base name. Then, use 'ActiveSheet.Copy' to move the sheet to a new workbook.
In the new workbook object, use the '.SaveAs' method. Specify your concatenated path and filename, and critically, set 'FileFormat:=xlOpenXMLWorkbook' (which correlates to the XLSX format).
Go back to your worksheet, navigate to the 'Developer' tab, click 'Insert', and choose 'Button (Form Control)'. Draw it on the sheet and assign your new macro to it.
Manually Save a Copy as XLSX
If you do not want to use VBA, you can manually save a copy of the open workbook in the XLSX format without affecting the original file.
Automate Your Workbooks with WPS Spreadsheet
WPS Spreadsheet provides robust support for macros and VBA, allowing you to automate repetitive tasks like file conversions easily. Experience high compatibility with Microsoft Excel files in a lightweight application.
- 1. Open Your XLSM File: Launch WPS Spreadsheet and open your macro-enabled workbook.
- 2. Access the Developer Tab: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic Editor'.
- 3. Insert the Macro Code: Paste your VBA code to copy the active sheet and save it as an XLSX file.
- 4. Run the Automation: Run the macro directly or assign it to a form control button for one-click file conversion.
Frequently Asked Questions
What is the VBA FileFormat code for an XLSX file?
The FileFormat property value for a standard XLSX file (Excel Workbook) is 'xlOpenXMLWorkbook', which corresponds to the numeric value 51.
Will my macros be saved in the new XLSX file?
No. By design, the XLSX format cannot contain macros. When you save an XLSM file as XLSX, all VBA code and macros are automatically stripped from the new file.
How do I assign my save macro to a button on the worksheet?
Go to the Developer tab, click Insert, and select 'Button (Form Control)'. Draw the button on your worksheet. When the 'Assign Macro' dialog box appears, select your newly created save macro and click OK.
Why am I getting an error when using SaveAs in VBA?
This often happens if the target filename already exists and Excel prompts for overwriting, or if you specify the '.xlsx' extension in the filename but use the wrong 'FileFormat' code. Ensure you use 'FileFormat:=xlOpenXMLWorkbook' when saving as XLSX.
