Excel VBA Save as File Format (12 Suitable Examples)

We store important information in Excel workbooks. We also perform various operations according to our requirements. So, it’s necessary to save the file once we update it. Otherwise, we may lose some valuable data. There are some effective ways to Save an Excel Workbook. Moreover, saving an Excel file as per our desired Format, or storing the file in the desired Location is another vital task. In this article, we’ll show you the most useful examples of Excel VBA Save as File Format.

Download Practice Workbook

Download the following workbook to practice by yourself.

12 Examples of Excel VBA Save as File Format

To illustrate, we’ll use the following dataset as an example. For instance, the dataset represents the Salesman, Product, and Net Sales of a company. Here, we’ll save this workbook by applying Excel VBA Save as File Format.

1. VBA to Save as Excel File

In our first example, we’ll show you a simple VBA Code to save an Excel file. Therefore, follow the steps below to perform the task.

STEPS:

• Firstly, go to the Developer tab.
• Then, select Visual Basic.

• As a result, the VBA window will pop out.
• After that, click Insert.
• Subsequently, select the Module.

• Hence, the Module window will appear.
• Afterward, copy the following code and paste it into the box.

Sub Example_1()
ActiveWorkbook.SaveAs
End Sub

• Now, run the code by pressing F5.
• Consequently, you’ll have to input the file name, format, and other info as asked.
• Lastly, it’ll save the file in your specified location.

Read More: How to Save a Worksheet as a New File Using Excel VBA

2. Specify File Extension with Excel VBA

In the previous example, we had to manually specify the File Format after pressing the run command. But here, we’ll show a more efficient way to apply Excel VBA Save as File Format to save workbooks in the desired format. We’ll simply specify the file extension after the file name. So, insert the below code in the Module window.

Sub Example_2()
Dim location As String
location = "D:\SOFTEKO\excel vba save as file format.xlsm"
ActiveWorkbook.SaveAs Filename:=location
End Sub

In this way, you can run the code to get the file in the desired format and location. To save the file in the xlsx Format, type xlsx in lieu of xlsm.

Read More: Excel VBA Macro to Save PDF in Specific Folder (7 Ideal Examples)

3. Excel VBA to Use File Format Code

However, we can input the File Format Code Number instead of specifying the file extension. Some useful codes are: .xlsx = 51, .xlsm = 52, .xlsb = 50, .xls = 56. Hence, copy the following code and paste it into the Module box.

Sub Example_3()
Dim location As String
location = "D:\SOFTEKO\excel vba save as file format"
ActiveWorkbook.SaveAs Filename:=location, FileFormat:=52
End Sub

Read More: [Fixed!] Why Is Excel Not Saving My Formatting? (7 Possible Reasons)

4. Save in Same Directory with VBA

In this example, we’ll show how to Save in the Same Directory where the file already is with Excel VBA. Therefore, insert the code in the Module window.

Sub Example_4()
ActiveWorkbook.SaveAs Filename:="excel vba save as file format"
End Sub

Read More: Excel VBA: Choose Location and Save as PDF

5. VBA to Store in New Directory

However, we may also need to save the file in a New Directory. Thus, type the following code in the Module box and run it.

Sub Example_5()
Dim location As String
location = "D:\SOFTEKO\excel vba save as file format"
ActiveWorkbook.SaveAs Filename:=location
End Sub

6. Ask for Password to Open Excel File

Additionally, you can apply Excel VBA Save as File Format to ask for a Password to Open Excel File. So, insert and run the below code.

Sub Example_6()
ActiveWorkbook.SaveAs _
Filename:="D:\SOFTEKO\excel vba save as file format.xlsm", Password:="one"
End Sub

Read More: How to Save Excel File with Password

Similar Readings

• Excel VBA to Save File with Variable Name (5 Examples)
• How to Save Excel as PDF Landscape (With Quick Steps)
• Excel VBA to Save as File Using Path from Cell (With Quick Steps)
• VBA Code for Save Button in Excel (4 Variants)
• [Fixed!] Excel CSV File Not Saving Changes (6 Possible Solutions)

7. Add Password for Editing in Excel

Moreover, you can ask for a Password for Editing in Excel. Without the password, it’ll only open in read-only format. Copy the code and paste it. Then, run the code.

Sub Example_7()
ActiveWorkbook.SaveAs _
Filename:="D:\SOFTEKO\excel vba save as file format.xlsm", WriteRes:="one"
End Sub

8. Open in Read-only Format

Again, for opening a file in Read-only Format, type the below code and press F5 to run it.

Sub Example_8()
ActiveWorkbook.SaveAs _
Filename:="D:\SOFTEKO\excel vba save as file format.xlsm", _
ReadOnlyRecommended:=True
End Sub

Read More: How to Open Word Document and Save As PDF or Docx with VBA Excel

9. Generate ‘Save As’ Dialog Box

Another useful operation of Excel VBA to Save as File Format is to generate Save As Dialog Box. Hence, insert the below code.

Sub Example_9()
Application.GetSaveAsFilename
End Sub

Read More: Excel Macro to Save as PDF (5 Suitable Examples)

10. VBA to Create & Save New Workbook

In addition to saving a file, we can also Create & Save New Workbook with VBA code. To accomplish the task, type the below code in the Module window and press F5.

Sub Example_10()
Dim book As Workbook
Set book = Workbooks.Add
Application.DisplayAlerts = False
book.SaveAs Filename:="D:\SOFTEKO\excel vba save as file format.xlsm"
Application.DisplayAlerts = True
End Sub

Read More: Excel VBA: Save Sheet as New Workbook without Opening

11. Save Active Workbook in Excel

Likewise, we can save the active workbook where it’s already stored. To carry out the operation, insert the very simple code.

Sub Example_11()
ActiveWorkbook.Save
End Sub

Read More: How to Save a Macro for All Workbooks in Excel (with Easy Steps)

12. VBA to Save as PDF Format in Excel

At last, we can use the PDF file extension in our VBA code to save in PDF Format. So, apply Excel VBA Save as File Format below. Then, run the code by pressing F5.

Sub Example_12()
ActiveSheet.SaveAs Filename:="excel vba save as file format.pdf"
End Sub

Read More: How to Save Excel as PDF (6 Useful Ways)

Conclusion

Henceforth, you will be able to save files with Excel VBA Save as File Format following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Save a Worksheet in Excel (2 Handy Approaches)
• How to Save Macros in Excel Permanently (2 Suitable Ways)
• [Fixed!] Document Not Saved Excel Network Drive (5 Possible Solutions)
• How to Save Excel as PDF Fit to Page (5 Easy Ways)
• Save Excel File as CSV (3 Handy Methods)
• How to Save Macro to Personal Macro Workbook?