Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to rename multiple files of a folder in Excel?

May be most of us are suffered with this problem that we need to rename multiple files in a folder, to rename the filenames one by one will make us crazy if there are hundreds or thousands files in that folder. Are there any good functions for us to deal with this task?

List all file names from a specific folder in worksheet with kutools for Excel

Rename multiple files of a folder in Excel with VBA code



If there are multiple files that you want to rename, first, you can list the old file names in a column of worksheet, and then enter the new filenames that you want to replace with. To quickly list all files in worksheet, you can use the Kutools for Excel’s Filename List utility.

Kutools for Excel : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days

After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now )

1. Click Enterprise > Import / Export > Filename List, see screenshot:

doc-rename-multiple-files-1

2. In the Filename List dialog box, click doc-rename-multiple-files-1 button to choose the file folder that you want to list the files, and then click All files from the Files type, see screenshot:

doc-rename-multiple-files-1

3. Then click OK button, all the filenames have been list in a column of a new worksheet, as well as some file attributes, and now, you can delete other unwanted columns and only leave the File Name column, see screenshot:

doc-rename-multiple-files-1

Click to know more about this Filename List feature…

Free Download Kutools for Excel Now


After listing all original filenames in one column A, and you should type new filenames in column B as following screenshot shown:

doc-rename-multiple-files-1

And here I will talk about a VBA code which may help you to replace the old filenames with the new filenames at once. Please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.

2. Click Insert > Module, and paste the following macro in the Module window.

VBA code: Rename multiple files in a folder

Sub RenameFiles()
'Updateby20141124
Dim xDir As String
Dim xFile As String
Dim xRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
If .Show = -1 Then
    xDir = .SelectedItems(1)
    xFile = Dir(xDir & Application.PathSeparator & "*")
    Do Until xFile = ""
        xRow = 0
        On Error Resume Next
        xRow = Application.Match(xFile, Range("A:A"), 0)
        If xRow > 0 Then
            Name xDir & Application.PathSeparator & xFile As _
            xDir & Application.PathSeparator & Cells(xRow, "B").Value
        End If
        xFile = Dir
    Loop
End If
End With
End Sub

3. After pasting the code, please press F5 key to run this code, and in the Browse window, select the folder which you want to change the filenames in, see screenshot:

doc-rename-multiple-files-1

4. And then click OK, all the old filenames have been replaced by the new filenames immediately. See screenshots:

doc-rename-multiple-files-1  2 doc-rename-multiple-files-1

Notes:

1. When you list your old and new filenames, the file extension must be included.

2. In the above code, the reference A:A indicates the old filenames list you want to rename, and reference B contains the new filenames that you want to use, you can change them as your need


Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related articles:

How to create sequence worksheets from a range of cells in Excel?

How to rename multiple worksheets in Excel?


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

View Features List Free Unlimited 60-day Trial See Price List

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jayakumar · 2 months ago
    Hi,

    I'm trying to use the code for renaming the non extension files to files with extension. it doesn't work. It works fine with files which have extension already.

    Thanks
    Jay
  • To post as a guest, your comment is unpublished.
    cda · 2 months ago
    Thank you so much...
  • To post as a guest, your comment is unpublished.
    Gee Kay · 5 months ago
    Hello, some of the files have same name. Scripts skip those files. Is there a way to modify the script so it adds a number 1, 2, 3 and so on for every duplicate name?
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hello, Kay,
      To rename the files based on duplicate new names with name-1,name-2, the following VBA code may help you, please try it.

      Note: After pasting this code, you should reference the Microsoft Scripting Runtime option in the References-VBAProject dialog box as below screenshot shown.

      Sub RenameFiles()
      Dim xDir As String
      Dim xFile As String
      Dim xVal As String
      Dim xRow As Long
      Dim xCount As Long
      Dim xTemp As Long
      Dim xDictionary As New Dictionary
      Application.ScreenUpdating = False
      On Error Resume Next
      With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      If .Show = -1 Then
      xDir = .SelectedItems(1)
      xFile = Dir(xDir & Application.PathSeparator & "*")
      Do Until xFile = ""
      xRow = 0
      xRow = Application.Match(xFile, Range("A:A"), 0)
      If xRow > 0 Then
      xCount = 0
      xVal = Cells(xRow, "B").Value
      If Not xDictionary.Exists(xVal) Then
      xDictionary.Add xVal, xCount
      Else
      xDictionary.Item(xVal) = xDictionary.Item(xVal) + 1
      xTemp = InStrRev(xVal, ".")
      Cells(xRow, "B").Value = Mid(xVal, 1, xTemp - 1) & "-" & CStr(xDictionary.Item(xVal)) & Mid(xVal, xTemp)
      End If
      Name xDir & Application.PathSeparator & xFile As _
      xDir & Application.PathSeparator & Cells(xRow, "B").Value
      End If
      xFile = Dir
      Loop
      End If
      End With
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Hemant · 11 months ago
    I am following the same process but couldn't succeeded in renaming the file i am also changing ("A:A") and ("B"). Is there any other valuve which i need to change in the code
  • To post as a guest, your comment is unpublished.
    Dee · 1 years ago
    I cant understand with range ( A:A) or (B) how to define that, thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Dee,

      As the note remind, the reference A:A indicates the old filenames list you want to rename, and reference B contains the new filenames that you want to use
      , as following screenshot shown:

      Hope this can help you, thank you!