Excel VBA Save As
After all the hard work we have done in the workbook we save it isn’t it? It is painful to lose the data we have worked on. We have two kinds of saves in excel or any file, one is “Save” and another one is “Save As”. Ctrl + S is the popular shortcut key as the Ctrl + C & Ctrl + V around the globe. But it is not that familiar a concept “Save As”. In regular worksheet shortcut key to “Save As the file is F12 key. In VBA too we can save the file as “Save As”. In this article, we will show you how to use the Save As a method in VBA.
What Does Save As Function Do in Excel VBA?
It is a general task of saving work in the automation process. After all the work we wish to save the file. In VBA we use the object “WORKBOOK” and use the method “Save” to save the file.
This code read Workbook named as “Sales 2019.xlsx” to save.
Similarly to replicate the workbook we are working on can be created by using “Save As” method in VBA.
4.6 (247 ratings)
- File Name: What is the name of the file you wish to give. This should be combined with file folder path.
- File Format: What is should be the format for the file you are saving.
- Password: Would you like to provide a password for the saving file.
- Write Res Password: Mention reserved password for the workbook.
I think these three parameters are enough to understand the Save As method.
How to Use Save As in Excel VBA?
What do we save??
We save workbook right, so it is important to mention the workbook name and its extension to use the “Save As” method in VBA. So mention the workbook you are saving.
Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx"). End Sub
Now use the Save As method.
Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs End Sub
Now identify where you want to save.
Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs "D:\Articles\2019 End Sub
Now put backslash and enter the file name as per your wish with a file extension.
Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs "D:\Articles\2019\My File.xlsx" End Sub
Now mention the file format as “xlWorkbok”.
Sub SaveAs_Example1() Workbooks("Sales 2019.xlsx").SaveAs "D:\Articles\2019\My File.xlsx", FileFormat:=xlWorkbook End Sub
Ok, we are done it will save the file in D drive > Folder Name (Articles) > Sub Folder Name (2019).
Save All the Opened Workbooks Using Save As
Assume you are working with 10 workbooks on your computer. You want to create back up of these workbooks by saving them in the computer as one of the copy. When you want to work with more than one workbook it is necessary to use the loops. Below code will save all the opened workbooks as SAVE AS in VBA.
Sub SaveAs_Example2() Dim Wb As Workbook For Each Wb In Workbooks ActiveWorkbook.SaveAs "D:\Articles\2019\" & ActiveWorkbook.Name & ".xlsx" 'Change the file path Next Wb End Sub
If you wish to select your own folder path then you can use this code.
Sub SaveAs_Example3() Dim FilePath As String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename:=FilePath & ".xlsx", FileFormat:=xlOpenXMLWorkbook End Sub
As soon as you run this code Using F5 key or manually then, it will ask you to select the destination folder path, select and click on OK, it will save the file.
This has been a guide to VBA Save As. Here we learn how to use save as a method to save an excel workbook along with examples & downloadable templates. Below are some useful articles related to VBA –