Excel VBA Save Workbook
In this article, we will see an outline on Excel VBA Save Workbook. It is very easy to create a macro for saving a Microsoft file. The code ranges from 1 line to 10 lines of code. And it is all up to us and our application which code we need to use. Although, a file can be saved just by pressing the shortcut key Ctrl+S. Or else click over the Save Icon which we can easily get at the top of the Excel file. That location is called the Title bar. And this is a location at the extreme left top corner on it. When the process of saving a file is so easy then why we need an extra step to make it happen. Reason can be different for different people, but major justification is when a huge data set in which we want to automate that fully or partially, saving the file after everything is done, may freeze the file. So it is better to save the file once the complete code is run, the process is done. By this, we can save extra time saving the huge data file in additional process and avoid file getting hung or crash. We will be seeing all the different types of code in upcoming examples.
How to Save Workbook in Excel VBA?
The following examples will teach us how to Save the Workbook in Excel by using the VBA Code.
VBA Save Workbook – Example #1
Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Now write the subprocedure for the VBA Save workbook or we can choose any name to define it.
Code:
Sub VBA_SaveWorkBook() End Sub
Step 3: Now to select the current workbook, use Active workbook as shown below followed by a dot.
Code:
Sub VBA_SaveWorkBook() ActiveWorkbook. End Sub
Step 4: Search Save function from the list.
Code:
Sub VBA_SaveWorkBook() ActiveWorkbook.Save End Sub
Now run the code by clicking on the Play button located below the menu bar.
Just to see whether the file is getting saved or not, keep VBA and Excel window parallel to each other. We will see, the file is now saved by showing the quick process of process.
VBA Save Workbook – Example #2
There is another way to save a workbook with one line of code. For this, we can use the same code which we have seen in example-1. For this, follow the steps below:
Step 1: Now in the same code, we will replace Active Workbook with ThisWorkBook. This is another way to select the current workbook which is selected. Now again run the code. We will see the file is save just by showing the waiting blue circle in quick seconds.
Code:
Sub VBA_SaveWorkBook2() ThisWorkbook.Save End Sub
Now moving to a little complex set of code but still also an easy way to save any workbook. For this, follow the steps below:
Step 2: Write the subprocedure for VBA Save Book as shown below.
Code:
Sub VBA_SaveWorkBook3() End Sub
Step 3: In this example, we will be using the For Each-Next loop to perform saving workbooks. Now define a variable using DIM as type Workbook.
Code:
Sub VBA_SaveWorkBook3() Dim Workbook As Workbook End Sub
Step 4: Now we will open a For loop write the condition for each defined variable Workbook activate the applications in the workbook.
Code:
Sub VBA_SaveWorkBook3() Dim Workbook As Workbook For Each Workbook In Application.Workbooks End Sub
Step 5: Now use Workbook along with function Save as shown below.
Code:
Sub VBA_SaveWorkBook3() Dim Workbook As Workbook For Each Workbook In Application.Workbooks Workbook.Save End Sub
Step 6: Close the loop by Next and using defined variable Workbook here as well.
Code:
Sub VBA_SaveWorkBook3() Dim Workbook As Workbook For Each Workbook In Application.Workbooks Workbook.Save Next Workbook End Sub
We can compile the complete code and run. We will notice again, quickly the code has saved the file again.
VBA Save Workbook – Example #3
There is another easy way to save the workbook using VBA code. For this, follow the below steps:
Step 1: Write the subprocedure for VBA Save Workbook.
Code:
Sub VBA_SaveWorkBook4() End Sub
Step 2: Here also, we will need a variable which we used in the previous example.
Code:
Sub VBA_SaveWorkBook4() Dim Workbook As Workbook End Sub
Step 3: Now use Set object along with a defined variable.
Code:
Sub VBA_SaveWorkBook4() Dim Workbook As Workbook Set Workbook = End Sub
Step 4: Then use Workbooks function and in brackets insert the name of the workbook which we want to save. Here the name of the workbook is “VBA Save Workbook” with extension. This should be the name of the file in which we are writing the code.
Code:
Sub VBA_SaveWorkBook4() Dim Workbook As Workbook Set Workbook = Workbooks("VBA Save Workbook.xlsm") End Sub
Step 5: Now use a Workbook variable with Save.
Code:
Sub VBA_SaveWorkBook4() Dim Workbook As Workbook Set Workbook = Workbooks("VBA Save Workbook.xlsm") Workbook.Save End Sub
Once done, run the code. We will notice the small waiting circle will appear for a few moments and the file is got saved.
Pros & Cons of VBA Save Workbook
- It is the easiest code in VBA to write.
- All the examples shown in the article are easy to implement.
- Not useful if used in a small set of code and for small data sets.
Things to Remember
- It is advised to use VBA Save Workbook code, once we complete the rest of the line of code or at the end of the entire code line.
- VBA Save Workbook is not limited to the examples which we have seen in the above article.
- Quote the name of the file in inverted commas always.
- Use macro enable excel format extension. It is the only version of MS Excel where we can save our VBA Code.
- Not advised to use this VBA Save Workbook in a small code structure. It will not give any major improvement in the selection of work.
Recommended Articles
This is a guide to VBA Save Workbook. Here we discuss how to Save Workbook in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
