What is VBA Name?

VBA NAME is a statement which can be used to move files from across drives and while moving the files we can rename the moving file as well.

What if you want to copy the file but paste with different name???
What is you want to copy the file but to delete the file in the original source folder??
With manual work we do all this without much trouble, but if your VBA project requires all of these to be done in the middle of the code execution how will you do??

In this article, we will show you how to do all of these using “Name Statement” in VBA. Read on…

What Does Name Statement Do in VBA?

When you hear the word “Name” what comes to your mind?? The first thing I got was “Worksheet Name” and “Workbook Name”.

Renaming can be done within the same directory or folder, not across drives. Let’s look at the syntax of the NAME statement.

Name OldName as NewName

Old Name: This is nothing but the current name of the file in the mentioned folder path.
New Name: While moving the file what should be the new name to be allocated to it?

One thing we need to remember is, NAME statement is used to move and rename the files not to create any file, folder or directory.

In order to move or rename the file, we need to first close the targeted file and execute the task or else we will get an error message.

How to Use VBA Name in Excel?

We will learn how to use a VBA Name Function with few examples in excel.

You can download this VBA Name Excel Template here – VBA Name Excel Template

Example #1 – VBA Name

Take a look at the simple example of changing the name of the File in the same folder. For example, I have a file in the below folder.

With this file, I want to rename the file “Sales April 2019.xlsx” to “April 2019.xlsx”. This can be done by using the NAME statement. Follow the below steps to apply the code.

Step 1: Add the new module.

Step 2: Start the subprocedure.

Code:

Sub FileCopy_Example1()

End Sub

Step 3: Declare two variables as a string.

Code:

Sub FileCopy_Example1()

Dim OldName As String
Dim NewName As String

End Sub

Step 4: For Old, Name Variable assign the folder path and file name with extension.

Code:

Sub FileCopy_Example1()

Dim OldName As String
Dim NewName As String

OldName = " D:\VPB File\April Files\New Excel\SalesApril.xlsx"

End Sub

Step 5: For the New Name variable we will mention the same path but will change the name of the file only.

Code:

Sub FileCopy_Example1()

Dim OldName As String
Dim NewName As String

OldName = " D:\VPB File\April Files\New Excel\SalesApril.xlsx"
NewName = " D:\VPB File\April Files\New Excel\April.xlsx"

End Sub

Step 6: Now I will use the NAME Statement.

Code:

Sub FileCopy_Example()

Dim OldName As String
Dim NewName As String

OldName = " D:\VPB File\April Files\New Excel\SalesApril.xlsx"
NewName = " D:\VPB File\April Files\New Excel\April.xlsx"

Name OldName As NewName

End Sub

Step 7: Old Name & New Name are the variables which hold path references. Now execute the code to see the magic.

In the same folder itself, it has changed the file name from “Sales April 2019.xlsx” to “April 2019.xlsx”.

This is the way of changing the file name in the same folder how about changing from one folder to a different folder.

Example #2 – Change from One Folder to Another

In order to move from one folder to another, we need to change the New Name path. For an example look at the below code.

Sub FileCopy_Example1()

Dim OldName As String
Dim NewName As String

OldName = "D:\VPB File\April Files\New Excel\April 1.xlsx"
NewName = "D:\VPB File\April Files\Final location\April.xlsx"

Name OldName As NewName

End Sub

If you observe the difference from our previous code to this code, we have changed the New Name to a different folder in the same drive.

Now I will execute this code and see the impact in both the folders.

New Folder:

In the new folder, we got the new sheet named as “April 2019.xlsx”. Now, look at the Old Folder.

Old Folder:

It says “This folder is empty”.

This is unlike our File Copy method. Name statement has removed the original file from the old folder and moved to a new folder with a new name as we specified.

Like this, we can move files from one folder to another with different names by using the “NAME” statement.