hypanis.ru Excel VBA Rename Files - BuffaloBI.com

Excel VBA Rename Files

Earlier this week, I received a request to provide a quick and easy way to rename a list of files in a specific folder.  Each month, the user gets a bunch of PDF files placed in this folder, then needs to manually rename each file for downstream use.  As you can imagine, this is a very tedious task, which must be repeated each month.  This is a great candidate for automation.

Luckily, Excel has a built-in statement to achieve this task.  The “Name” statement renames a disk file, directory, or folder.  Combined with a For Each Loop and some parameter manipulation, the task of renaming files takes no time at all!  The following example illustrates this technique.

Excel VBA Rename Files – Setup

First, determine which folder contains the files you need to rename.  In this simple example, I have the following:

Excel VBA Rename Files

Next, setup your Excel file, using the following macro to generate the list of files you want to rename:

Once you run this code, your Excel file will look like this:

Excel VBA Rename Files

Add the new file name in column B:

Excel VBA Rename Files

Next, add the following macro to your VBA project:

Finally, execute the macro code above and you will see the newly named files in your target folder:

Excel VBA Rename Files

As you can see, the files are renamed to the desired text in a split second!

Please modify the above code to suit your needs.

Note:  This example was created in Excel 2013.



Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA



4 thoughts on “Excel VBA Rename Files”

  1. Thanks for posting. When I try to update the path to my Mac I get an error that the path can’t be found yet the excel table I’m working with and the files I want to rename are there. Any help would be appreciated!

    Sub RenameFiles()

    Dim strPath As String
    strPath = “/Users/azwhite/Downloads/”

    Dim srcrng As Range
    Set srcrng = Range(“A2”, ActiveSheet.Range(“A2”).End(xlDown))

    For Each cell In srcrng

    Name strPath & cell.Value As strPath & cell.Offset(0, 1).Value

    Next cell


    End Sub

  2. Thanks for the post, this is close to what I’m trying to accomplish. Would it be possible to prompt the user for a path, list the files in selected path, then give user ability to rename the files and select a destination path?


Leave a Comment