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.

Excel VBA Save as File Format: 12 Examples

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: Excel VBA to Save as File Using Path from Cell

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: How to Use Macro to Save Excel File with New Name

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: How to Save Excel Macro Files as Filename from Cell Value 

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

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

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

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

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

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

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: Excel Macro to Save as PDF

Download Practice Workbook

Download the following workbook to practice by yourself.

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Create New Workbook and Save Using VBA in Excel
• Excel VBA to Save File with Variable Name
• Excel VBA: Save Workbook as New File in Same Folder
• Excel VBA to Save Workbook in Specific Folder with Date
• Excel VBA: Save Workbook in Specific Folder
• Excel VBA: Save Workbook Without a Prompt