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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Save Excel File with Variable Name Using VBA: 5 Practical Examples

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.

Read More: Excel VBA: Save Workbook as New File in Same Folder


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.

Read More: How to Use Macro to Save Excel File with New Name


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.

Read More: Excel VBA: Save Workbook in Specific Folder


4. Specify File Type in VBA Code before Saving

In the previous example, we needed to input the file format or type while 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.

Read More: Excel VBA to Save as File Using Path from Cell


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.

Read More: How to Save Excel Macro Files as Filename from Cell Value


Download Practice Workbook

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


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. You can ask any Excel-related problem and give your suggestions in the comment box.


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.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo