Excel VBA to Save a File with Variable Names – 5 Examples

Example 1 – Using a VBA Code to Save a File in the Current Location with a New File name

Steps:

• Go to Sheet Name and right-click.
• Choose View Code.

• The VBA window opens.
• In the Insert tab, choose Module.

• Enter the following VBA code.

Sub SaveFile_1()
Dim File_Name As String
File_Name = "Exceldemy_1"
ActiveWorkbook.SaveAs FileName:=File_Name
End Sub

The file name is specified in the code.

• Press F5 to run the code.

The file name changed.

Dim File_Name As String-declares a variable.

File_Name = "Exceldemy_1"-sets the value of the variable.

ActiveWorkbook.SaveAs FileName:=File_Name-saves the workbook taking the name of the variable.

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

Example 2 – Using a VBA Code to Save a File in a New Location with a New File name

Steps:

• Copy the address from the address bar.

Both file and VBA code will be saved here.

• Enter the VBA code in the module (see Example1).

Sub SaveFile_3()
ActiveWorkbook.SaveAs FileName:="D:\OneDrive\Softeko\25-0145_4019\Exceldemy_3"
End Sub

• Press F5 to run the code.

The file is saved with a new name in the selected location.

ActiveWorkbook.SaveAs FileName:="D:\OneDrive\Softeko\25-0145_4019\Exceldemy_3"
-saves the active workbook on a given location.

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

Example 3 – Using the VBA GetSaveAsFilename Function to Save a File

Steps:

• Enter this VBA code 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

• Press  F5 to run the code.
• Choose a location.
• Go to File name and select a name. Choose a type of file.
• Click Save.

This is the output.

Dim File_Name As Variant-declares a variable.

File_Name = Application.GetSaveAsFilename-sets a variable name from the file explorer saving.

If File_Name <> False Then
  ActiveWorkbook.SaveAs FileName:=File_Name
End If-opens a dialog box to enter the file name.

Read More: Excel VBA: Save Workbook in Specific Folder

Example 4 – Specifying the File Type in the VBA Code before Saving

Steps:

• Copy the VBA code below and paste it in the module.

Sub SaveFile_4()
Dim File_Path As String
File_Path = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileName:=File_Path & ".xlsm"
End Sub

• Press F5 to run the code.
• Choose a location.
• Enter the name in File name.
• Click Save.

• Go to the location where the file was saved.

It is saved as Macro-Enabled.

Dim File_Path As String

File_Path = Application.GetSaveAsFilename-sets a variable name from the file explorer saving.

ActiveWorkbook.SaveAs FileName:=File_Path & ".xlsm"-saves the file with the variable name and path.

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

Example 5 – Inputting a File name from a Cell and Saving the File Instantly

Steps:

• A name was placed in B12.

• Paste the VBA code below in 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

• Press F5 to run the code.

• Go to the location shown in File Explorer.

Dim Shell_1 As Object
Dim File_name, Full_path As String-declares 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"-sets the value of thevariables.

ActiveWorkbook.SaveCopyAs Full_path-saves the active workbook on the location of the

 variable.

MsgBox ("File is saved at " + Full_path)shows 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.

Related Articles

• Excel VBA: Save Workbook Without a Prompt
• Excel VBA to Save Workbook in Specific Folder with Date
• How to Save a Copy as XLSX Using Excel VBA
• Create New Workbook and Save Using VBA in Excel
• Excel VBA Save as File Format