·
4 months ago
Hi there
It does not work with unicode file names. How can it be done fine?
It does not work with unicode file names. How can it be done fine?
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.
| Kutools for Excel : with more than 300 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:
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
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?
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 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!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 1 years agoHi,
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.
· 1 years agoThank you so much...
-
To post as a guest, your comment is unpublished.
· 1 years agoHello, 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.
· 1 years agoHello, 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.
· 1 years agoI 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.
· 1 years agoI cant understand with range ( A:A) or (B) how to define that, thank you
-
To post as a guest, your comment is unpublished.
· 1 years agoHi, 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!
-
-
To post as a guest, your comment is unpublished.
· 1 years agoI would like to rename multiple files from folders and subfolders. So im thinking a VBA code there looks at the path there is in the Column 'Containing Folder' (I Check the checkbox 'Include files in subdirectories') I am no where near good enough for coding VBA, but i look and try to understand. Thanks
-
To post as a guest, your comment is unpublished.
· 2 years agoThis is game changing. Thank you SO MUCH!!!
-
To post as a guest, your comment is unpublished.
· 2 years agoTo rename the multiple files you can also check a software. Search on google- BatchRenameFiles Tool. Check the first search result.
Thanks
Jonathan F. -
To post as a guest, your comment is unpublished.
· 2 years agohi, Amazing codes, has saved my hell lot of time.. thanks a ton..
-
To post as a guest, your comment is unpublished.
· 2 years agoi want to make combo of pictures for example i have 33 images and i want to create a combo of pictures with each other, please find attched i have 10000 more images so that i can create in a 1 click of multiple images.
please call or email at caparnasingla@gmail.com -
To post as a guest, your comment is unpublished.
· 2 years agoI suggest to try KrojamSoft BatchRename
-
To post as a guest, your comment is unpublished.
· 2 years agoI tried it for excel, it changes file type.Can you please provide me code for csv files rename.
-
To post as a guest, your comment is unpublished.
· 2 years agoDear all,
Try KrojamSoft BatchRename for batch rename the files its very easy to use. -
To post as a guest, your comment is unpublished.
· 2 years agoThank you so much sir,
God bless you! -
To post as a guest, your comment is unpublished.
· 3 years agoHi,
I have more than 100 excel files which I have downloaded from a system and kept in the folder.
All these files has a sheet named as [u][b]Content[/b][/u]. I want these excel files to be named using the text that is available in the Cell AA2 of the respective [b][u]Content[/u][/b] sheet. Such that the name in this Cell AA2 will be exactly the File name of that respective Excel file.
all the files in the folder should get renamed in the same way.
Please suggest a macro that can work in this way.-
To post as a guest, your comment is unpublished.
· 2 years agoHi. Is your task already Solved. ? I can help if still unsolved. Pls mail me your task. Tx Franz
-
-
To post as a guest, your comment is unpublished.
· 3 years agoI successfully used the above instructions last year to rename files in half-a-dozen folders. It worked so well that I saved the instructions for future use. I've recently tried to use it again, but after pasting the macro above in the Module window when I hit F5 instead of getting a browser window to select the desired folder, I get a new dialog box prompting me for a Macro name. Any suggestions? I usually have about 3-4 times a year where I need to rename files this way, and I was very excited when it worked last year.
-
To post as a guest, your comment is unpublished.
· 3 years agoIs it possible rename just a piece of a file name without declaring the full file name and extension in the excel sheet? i.e. change 123-yz.jpg to abc-yz.jpg but I only know that 123 needs changing to abc on every image in the folder?
-
To post as a guest, your comment is unpublished.
· 4 years agoSurprised (a little) to see someone use the same syntax for naming vars (xDir, for example). It allows for exceptionally well named vars that don't conflict with system names (e.g., Dir or Date). I also use 'z' for global functions (e.g., zMsg, that returns true or false rather than vbOK or vbCancel) and 'y' for constants (e.g., yCR for vbNewline and yCR2 for two of them).
Good work and good thinking on your part. You must be very smart! -
To post as a guest, your comment is unpublished.
· 4 years agoHow to rename files which contains chinese characters in name.
Please help on it -
To post as a guest, your comment is unpublished.
· 4 years agoNevermind, i figured it out. Thanks. " xRow = Application.Match(xFile, Range("C:C"), 11)"
-
To post as a guest, your comment is unpublished.
· 4 years agoWhat do i need to modify in the code above if my old and new names do not start until Row 11. I changed this line "xRow = Application.Match(xFile, Range("C11:C5000"), 0)" but it didn't work.
-
To post as a guest, your comment is unpublished.
· 4 years agoDear all,
How can I change FOLDERS the same way as shown above?
Could you kindly help me with this issue?
The question is: How to rename multiple FOLDERS of a folder in Excel?
Kind regards!
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
한국어
Português
Русский
Español