In this tutorial we will guide you on how to save files in Excel using VBA. Visual Basic for applications (VBA) is a powerful programming language designed and developed by Microsoft. It is integrated in many Microsoft Office applications like Excel, Access etc. to helps users to create their own functions to perform specific tasks. Here we are showing you how a “Save as” option works by using Excel VBA. Instead of using the “Save as” button, we will record a macro and use the double click option in VBA Excel for save as function.
Step 1 – Choose the Worksheet and Event handler
To create a VBA Excel Save As macro, we start with a workbook with data entered regarding the inventory. Now let’s save the file in D drive in a folder named Inventory.
So let’s start by double clicking on the “Sheet1” module, selecting “worksheet” from the Object drop down list and choosing “Before double click” for the event handler.
Step 2 – Insert variables
We will begin by assigning variables for the “Path” and the “File name” as strings.
Step 3 – Insert a Path and range for selecting the File name to save
Now we will define the Path of the Inventory folder which is located in the D drive.
Since we are trying to automate the process, let’s write a line of code which will automatically set the file name instead of prompting us for suggestions.
Over here, we want the data in cell A1 to be kept as the file name, and file name by using the Range of Cell “A1”. For that, we have mentioned the range over here.
Step 4 – Enter the code for Save as function
Next, we will use the Active Sheet.SaveAs property and define the file name, path, file extension and file format over here. For the path, notice that we have simply called upon the variable that was used to store the location before.
Step 5 – Open up the selected directory for the saved file
Now if you go back to the Excel Sheet and double click anywhere, the file will automatically be saved. Now if we access the location mentioned in the path variable, you will notice that an excel file is present over there, with the same file name which was present in cell A1.
That means the file has been saved successfully in Excel using VBA.
Script axample can be found here:
https://howtech.tv/wp-content/images/004608/130715_5_Script.txt
Home