EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Name

By Ashwani JaiswalAshwani Jaiswal

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA Name

Excel VBA Name

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…

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (2,356 ratings)
Course Price

View Course

Related Courses

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.

Changing the Name

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.

VBA Name Module

Step 2: Start the subprocedure.

Code:

Sub FileCopy_Example1()

End Sub

VBA Name - subprocedure

Step 3: Declare two variables as a string.

Code:

Sub FileCopy_Example1()

Dim OldName As String
Dim NewName As String

End Sub

VBA Name - String

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

File Location

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

New Name variable

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

Using the Name Statement

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

New File

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

Copy Paste

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:

VBA Final Location

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

Old Folder:

Old file

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.

Recommended Articles

This is a guide to VBA Name. Here we discuss how to use Excel VBA Name Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA RGB
  3. VBA Subscript out of Range
  4. FileCopy in VBA

All in One Excel VBA Bundle (120+ Courses)

120+ Online Courses

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VBA Name Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - All in One Excel VBA Bundle (120+ Courses) Learn More