In Excel, you can save a file using the VBA code. You can do it by specifying the path and name of a file in code, or by opening Save As window. In this tutorial, I’m going to show you both ways. You can quickly save an XLSX file using
this single line of code. In this case, you have to give a path and file name where the program should save the file. In this code, the file name is given with the XLSX extension. You can drop it because there is specified the file format code: 51. This code means that the file will be saved in this format. If you don’t want to place the name of the file in code each time you run the
script, you can open a SaveAs window asking the user for the name and path. This is the same window when you saving an Excel file for the first time or using Save As to choose a different name. If you want to be sure
that the file is saved correctly, you can display a box with the success message and file path of the file location. After you run the code, the Save As will appear. Save the file to display the message.A quick way to save a file in VBA
Open the SaveAs window
Display information after successful save
Post Views: 29,101
See all How-To Articles In this tutorial, you will learn how to save a macro-enabled workbook in Excel and Google Sheets.
Save a Macro-Enabled Workbook (.xlsm)
In Excel you can create a macro, using the macro recorder. If you are working with standard Excel file type (.xlsx), save it as a macro-enabled workbook (.xlsm) to be able to use macros.
Note that this isn’t necessary with the old Excel type (.xls), as it’s already macro-enabled.
Create a Macro
Macros can help you to reduce repetitive work in Excel by recording a set of actions and repeating them by running the macro. To record a macro, follow these steps:
1. In the Ribbon, go to View > Macros > Record Macro.
2. In the Record Macro window, (1) enter a macro name and a (2) shortcut key, then (3) click OK.
3. Make changes in the sheet that should be performed by the macro (change in fonts, colors, sizes, etc.)
4. In the Ribbon, go to View > Macros > Stop Recording.
Save a File With the .xlsm Extension
Now save the file as macro-enabled to be able to use the recorded macro later.
1. In the Ribbon, go to File > Save As.
2. In the save window, select file type Excel Macro-Enabled Workbook (*.xlsm), and click Save.
Finally, the file is saved as macro-enabled, and you can use all macros recorded within it.