Excel VBA to Save File with Variable Name (5 Examples)

When creating a new Excel file first, we save the file in a certain location and work with that. Besides using conventional ways, we can also save a file using Excel VBA. In this article, we will show some VBA examples with a variable name to save an Excel file with proper illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 VBA Examples to Save Excel File with Variable Name

Before starting to explore the next sections, remember that we are working with a file that is already saved once in a folder. We will save the changes in the folder using this code with a new file name from the time of applying the code.

Now, let’s start.

1. VBA Code to Save a File in Current Location with a New Filename

In this example, we will show how to save a file with a variable name in the default location. The name will be given in the code directly and saved in the predefined folder.

📌 Steps:

  • First, go to the Sheet Name section at the bottom of each sheet.
  • Press the right button of the mouse.
  • Choose the View Code option from the Context Menu.

VBA Code to Save a File in Current Location with a New Filename

  • The VBA window appears.
  • Choose the Module option from the Insert tab.

VBA Code to Save a File in Current Location with a New Filename

  • We entered the VBA window. We will write and run VBA code from this window.

  • Copy and paste the following VBA code on the module.
Sub SaveFile_1()
Dim File_Name As String
File_Name = "Exceldemy_1"
ActiveWorkbook.SaveAs FileName:=File_Name
End Sub

VBA Code to Save a File in Current Location with a New Filename

Here, notice the file name specified in the code.

  • Now, run the code by pressing the F5 button.

We can see the name has changed. We did not give any location to save this file. It is saved on the default location of my computer.

🔎 Code Explanation:

Dim File_Name As String

Declare a variable.

File_Name = "Exceldemy_1"

Set the value of the variable.

ActiveWorkbook.SaveAs FileName:=File_Name

Save the workbook and take the name from the variable.


2. VBA Code to Save File in a New Location with New Filename

In this example, we will input both the file location and the name directly on the code. Now, carry out the following steps to see what happens with the Excel file here. Apply the steps below.

📌 Steps:

  • First, we will copy the address of the location from the address bar of the File Explorer.

VBA Code to Save File in a New Location with New Filename

We will save our desired file at this location. And use this location on the VBA code.

  • Put the VBA code that contains the name with location on the module (see Example 1 for detailed steps).
Sub SaveFile_3()
ActiveWorkbook.SaveAs FileName:="D:\OneDrive\Softeko\25-0145_4019\Exceldemy_3"
End Sub

VBA Code to Save File in a New Location with New Filename

  • Now, press F5 to run the code.

We can see that file is saved with the new name.

  • Go to the File Explorer. Our file is saved in the exact location we put on the code.

VBA Code to Save File in a New Location with New Filename

🔎 Code Explanation:

ActiveWorkbook.SaveAs FileName:="D:\OneDrive\Softeko\25-0145_4019\Exceldemy_3"

Save the active workbook on the given location.


3. VBA GetSaveAsFilename Function to Save File

In this VBA code, will use the GetSaveAsFilename function to save the file in a new location with a new name. Remember that, this code will allow you to specify the filename from Save As window of file explorer. And you have to write the file extension along with the filename in the corresponding box. To understand how to do these all, execute the steps below.

📌 Steps:

  • Put the VBA code below in a new module.
Sub SaveFile_2()
Dim File_Name As Variant
File_Name = Application.GetSaveAsFilename
If File_Name <> False Then
  ActiveWorkbook.SaveAs FileName:=File_Name
End If
End Sub

VBA GetSaveAsFilename Function to Save File

  • Now, press the F5 button to run the code.
  • We enter the File Explorer. Choose our desired location.
  • Put a name with file type on the File name box.
  • Finally, click the Save button.

VBA GetSaveAsFilename Function to Save File

  • Have a look at the Excel worksheet.

VBA GetSaveAsFilename Function to Save File

The file has been saved with the new name.

🔎 Code Explanation:

Dim File_Name As Variant

Declare a variable.

File_Name = Application.GetSaveAsFilename

The file name will be taken from the file explorer saving.

If File_Name <> False Then
  ActiveWorkbook.SaveAs FileName:=File_Name
End If

Apply If loop. A dialog box will appear to input the file name.


4. Specify File Type in VBA Code before Saving

In the previous example, we needed to input the file format or type during saving the file. But now we will mention the file type in the code.

📌 Steps:

  • Copy the VBA code below and put it on the module.
Sub SaveFile_4()
Dim File_Path As String
File_Path = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileName:=File_Path & ".xlsm"
End Sub

Specify File Type in VBA Code Before Saving in Excel

  • Then run the code by pressing the F5 button.
  • File Explorer appears now. Go to the desired location.
  • Put the name to save on the File name box.
  • Finally, click on the Save button.

Specify File Type in VBA Code Before Saving in Excel

  • Now, go to the location where the file is saved.

Notice that the file is saved as Macro-Enabled.

🔎 Code Explanation:

Dim File_Path As String

Declare a variable.

File_Path = Application.GetSaveAsFilename

Set variable name from the file explorer saving.

ActiveWorkbook.SaveAs FileName:=File_Path & ".xlsm"

It will save the file with the variable name and path.


5. Input Filename from a Cell and Save File Instantly

You can specify the filename within your dataset and save your file instantly with that name using the following VBA code. Apply the steps below for this.

📌 Steps:

  • We can see a name is placed on Cell B12.

  • Now, paste the VBA code below on the module.
Sub SaveFile_5()
Dim Shell_1 As Object
Dim File_name, Full_path As String
Set Shell_1 = CreateObject("WScript.Shell")
DeskTop_Path = Shell_1.SpecialFolders("Desktop")
File_name = Range("B12").Value
Full_path = DeskTop_Path + "\" + File_name + ".xlsm"
ActiveWorkbook.SaveCopyAs Full_path
MsgBox ("File is saved at " + Full_path)
End Sub

Input Filename from a Cell and Save File Instantly in Excel

  • Then, press the F5 button to run the code.

Input Filename from a Cell and Save File Instantly in Excel

A pop-up is showing the file location.

  • Go to the location shown on the File Explorer.

Input Filename from a Cell and Save File Instantly in Excel

We get the desired file.

🔎 Code Explanation:

Dim Shell_1 As Object
Dim File_name, Full_path As String

Declare multiple variables of two types.

Set Shell_1 = CreateObject("WScript.Shell")
DeskTop_Path = Shell_1.SpecialFolders("Desktop")
File_name = Range("B12").Value
Full_path = DeskTop_Path + "\" + File_name + ".xlsm"

Set value of variables.

ActiveWorkbook.SaveCopyAs Full_path

Save the active workbook on the location of the  Full_path variable.

MsgBox ("File is saved at " + Full_path)

Show the file location as a pop-up.


Conclusion

In this article, we showed 5 examples of Excel VBA to save the file with a variable name. We also showed where to save those files. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo