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
List all file names from a specific folder in worksheet with kutools for Excel
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.
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now )
1. Click Kutools Plus > Import & Export > Filename List, see screenshot:
2. In the Filename List dialog box, click 
button to choose the file folder that you want to list the files, and then click All files from the Files type, see screenshot:
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:
Click to know more about this Filename List feature…
Free Download Kutools for Excel Now
Rename multiple files of a folder in Excel with VBA code
After listing all original filenames in one column A, and you should type new filenames in column B as following screenshot shown:
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:
4. And then click OK, all the old filenames have been replaced by the new filenames immediately. See screenshots:
 |
 |
 |
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
Demo: Rename multiple files of a folder in Excel
Related articles:
How to create sequence worksheets from a range of cells in Excel?
How to rename multiple worksheets in Excel?
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
