Excel VBA Save as File Format (12 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

VBA to Save as an Excel File

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

VBA to Save as an Excel File

  • 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

Specify File Extension with Excel VBA

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

Excel VBA to Use File Format Code

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

Save in Same Directory with VBA


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

VBA to Store in New Directory


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

VBA to Create & Save New Workbook


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

VBA to Save as PDF Format in Excel

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

1 Comment
  1. Sorry for my limited knowledge. You should show the result for each VBA….Thanks.
    Right now, I have some problem on excel in window 11.
    ‘Text to column’ is necessary after Copy & Paste.
    I do not want to do ‘text to column’ manually.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo