Some of the tasks I had to do at work involved running a couple of “master” spreadsheet models every month for new market conditions, for a lot of clients. Running the models took ages, but the part I loathed the most was, I had to rename each workbook individually. I figured, my time is far too valuable and created an Excel Spreadsheet Application to rename the files for me.
In this post, I will show you how to use the FileDialogue to select a folder and save its path to a cell. Then we will use the FileSystemObject, File and Folder objects to list the files in the spreadsheet and rename them from within Excel. Finally, we will combine these techniques to create a spreadsheet application that you can use to replace a string in the filenames of all the files in a folder.
Step 1: Fetching the Folder path
- The First step is to write a function thatusesFileSystemObject to fetch the path
'==================================================================================== 'A fairly generic Function to fetch a Forlder's Path 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 01 January 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Function OpenFolder(Optional ByVal AllowMulti As Boolean = False) As String 'Declare the FileDialog Object Dim diaFolder As FileDialog On Error Resume Next 'Standard Error Handling code 'Set the FileDialogue to be able to select only folders Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) 'Set AllowMultiSelect property to true to allow the user 'to select multiple files diaFolder.AllowMultiSelect = AllowMulti 'Show the Dialog box diaFolder.Show 'Return the file path OpenFolder = diaFolder.SelectedItems(1) & "\" 'Empty the object Set diaFolder = Nothing Err.Clear 'Standard Error Handling code On Error GoTo 0 'Standard Error Handling code End Function
- Then we write a simple macro that writes the path to a cell in the sheet. Please feel free to download the Workbook and snoop around, for some of the ancillary macros won’t make complete sense unless you take a look at the actual spreadsheet layout. Click the image below to download the file.
Step 2: Listing files inside selected Folder
- Subsequently, we write a macro to list all the files in the selected folder into a range in the spreadsheet.
'==================================================================================== 'A macro that uses FileSystemObject to access the names of all the files in a folder 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 01 January 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub GetFiles(Optional ByVal booDummy As Boolean = True) 'Declare the FileSystemObject Objects Dim objFSO As FileSystemObject Dim objFolder As Folder Dim objFile As File 'Declare ancillary variables Dim strPath As String Dim lngCount As Long 'Specify the path to the folder strPath = shtTool.Range("FilePath").Value2 'Create an instance of the FileSystemObject Set objFSO = CreateObject("Scripting.FileSystemObject") 'Get the folder On Error Resume Next 'Standard Error Handling code Set objFolder = objFSO.GetFolder(strPath) If Not Err.Number = 0 Then 'Standard Error Handling code MsgBox "Cannot find Folder. Please Check if the specified folder exists." & _ vbNewLine & "VBA Error Description : " & Err.Description Err.Clear 'Standard Error Handling code End End If On Error GoTo 0 'Standard Error Handling code 'If the folder does not contain files, exit the sub If objFolder.Files.Count = 0 Then MsgBox "No files were found...", vbExclamation Exit Sub End If 'Set the Counter to 1 lngCount = 1 'Loop through each file in the folder For Each objFile In objFolder.Files 'Use the counter and named ranges to fill up the sheet. 'Please feel free to accomplish this however you see fit. 'This is my preferred method, for various self percieved advantages. shtTool.Range("FileNumber")(1).Offset(lngCount - 1).Value = lngCount shtTool.Range("FullPath")(1).Offset(lngCount - 1).Value = objFile.Path shtTool.Range("FileName")(1).Offset(lngCount - 1).Value = RemoveExtension(objFile.Name) shtTool.Range("Extension")(1).Offset(lngCount - 1).Value = FileExtension(objFile.Name) shtTool.Range("DateLastModified")(1).Offset(lngCount - 1).Value = Format(objFile.DateLastModified, "D MMM YYYY") shtTool.Range("NewName")(1).Offset(lngCount - 1).Formula = "=Prefix&SUBSTITUTE(FileName,ReplaceWhat,ReplaceWith)&Suffix" shtTool.Range("Override")(1).Offset(lngCount - 1).Value = vbNullString shtTool.Range("Override")(1).Offset(lngCount - 1).NumberFormat = "General" shtTool.Range("FinalName")(1).Offset(lngCount - 1).Formula = "=IF(ISBLANK(Override),NewName,Override)&Extension" 'Incriment the counter lngCount = lngCount + 1 Next objFile End Sub
Step 3: Design the Supporting structure to compose the new name
- A few of the things you might want to do to file names are appending text to the beginning or end of the file name; replace text in the file name with something else. These are the functionalities that I have added to my application.
- Note that adding a suffix needs theextensiontobeseperated out first and I have used the following functionstoaccomplish that:
'==================================================================================== 'Function to get the file extension 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 01 January 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Function FileExtension(ByVal strfilename As String) As String FileExtension = "." & Right(strfilename, Len(strfilename) - InStrRev(strfilename, ".", , vbTextCompare)) End Function '==================================================================================== 'Function to remove the file extension 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 01 January 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Function RemoveExtension(ByVal strfilename As String) As String RemoveExtension = Left(strfilename, InStrRev(strfilename, ".", , vbTextCompare) - 1) End Function
Step 4 : Write a macro to Rename the Files using ‘FileSystemObject’
- We add in the code that actually renames the files:
'==================================================================================== 'A macro that uses FileSystemObject to Rename all the files in a folder 'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com 'Date: 01 January 2013 'Website : http://strugglingtoexcel.wordpress.com/ '==================================================================================== Sub RenameFiles(Optional ByVal booDummy As Boolean = True) 'Recalculate workbook to make sure the formula that composes the new names are 'Calculated shtTool.Calculate 'Declare the FileSystemObject objects Dim objFSO As FileSystemObject Dim objFile As File 'Create an instance of the FileSystemObject Set objFSO = CreateObject("Scripting.FileSystemObject") 'Declare and Initialize ancillary variables Dim rngFullPath As Range Dim rngFinalName As Range Set rngFullPath = shtTool.Range("FullPath") Set rngFinalName = shtTool.Range("FinalName") Dim lngCount As Long On Error Resume Next 'Standard Error handling code 'Loop through the Files imported into the spreadsheet and Renaming them. For lngCount = 1 To rngFullPath.Count Set objFile = objFSO.GetFile(rngFullPath(lngCount).Value) 'Error handling for files that are not available If Not Err.Number = 0 Then 'Standard Error handling code MsgBox "File Not Found." & vbNewLine & "VBA Error Description : " & Err.Description Err.Clear End End If objFile.Name = rngFinalName(lngCount).Value 'Error handling for invald file name If Not Err.Number = 0 Then 'Standard Error handling code MsgBox "Invalid Filename." & vbNewLine & "VBA Error Description : " & Err.Description Err.Clear End End If Next lngCount On Error GoTo 0 'Standard Error handling code End Sub - Finally we assign all these macros to suitable buttons.
Instructions to use the Application:
- Download the file from the section below.
- Click the ‘ImportFiles‘ Button to browse for the folder that has the files you would like to rename.
- If you have changed any of the file names manually or added a few more files after importing them to the spreadsheet, you can click the ‘Refresh File List‘ button. This lists the files in the path selected earlier, without having to browse for it again.
- Add in the rules to the ‘Replace What‘, ‘Replace With‘, ‘Prefix‘, and ‘Suffix‘ ranges. You may also override the names with a custom rule or text by specifying it in the ‘Override‘ column. I have modified the file name to replace ‘December 2013‘ with ‘January 2014‘; Prefix ‘Model‘ to the filename; append ‘ – Done‘ to the end. Note that I have overridden the name for the third file. Do note that they will be reset after the files are renamed.
- Finally click the ‘Rename Files‘ button. This renames all the files, and lists the new file names. You may further modify the file names or choose to reset the tool by clicking the ‘Reset‘ button.
I saved this workbook in Excel 2003 format, considering the fact that most organizations may not have upgraded to the latest version of Microsoft Office yet. There may be some formatting related compatibility issues when opened in Excel 2003, do let me know if hinders accessibility.  The workbook has a lot of supporting macros and hidden cells to make it user friendly. I have also incorporated a few self learned practices into this application, I will be writing dedicated posts about them in the near future. I have left the code unprotected, please feel free to change it however you please. However, when you develop a full fledged spreadsheet application, make sure you lock it down.
Download
Fantastic.
What if i need to have worksheet TAB name (to be listed on the same sheet) and rename TAB name.
Pls let me know if you can help
Regards,
Sajjad
Hi Sajjad,
I am glad that you liked it. I can tweak this application to list the worksheets in a workbook, and rename the sheets.
How urgently do you need it?
If you are interested in doing it yourself, I can give you some pointers.
Pls go ahead, give me some pointers.
I would like to try out myself
First we need to choose which ‘WorkBook’ to work with. Make sure it is open.
Then we could use a For Each loop to go through all the ‘WorkSheets’ in the workbook and list them on a sheet. I would use named ranges to do this.
Then I would have another column where the user can type in the new name.
Finally I would create another macro that runs through all the items, and does something like this:
Workbook.Worksheets(“OldName”).Name = “NewName”
You can go through this Batch Renamer Application to get an idea of how to implement it with Named Ranges and formatting and all the fancy stuff. If you just want something that works, you can always use the Offset function.
Pingback: Using the EURO symbol in filenames via an excel generated RENAME statement in a batch file ...!
You tool is awesome!! How can I adjust it to loop through all folders in a directory and list all files to be renamed?
Example:
My Folder
Sub Folder 1
File
File
File
File
Sub Folder 2
File
File
File
Sub Sub Folder 2
File
File
File
File
File
File
etc….
Hi Wally. I am glad you like it. That is a great question actually, I should add that functionality into this workbook.
One way to do it, I have seen a lot of developers do this, is to recursively call the same function that lists files, if it encounters a folder. You may have to use a public long variable to keep track of the row number you are writing to (or you can use the End+offset combination to get the the last written row.
Give it a shot and let me know how it goes. I will implement it in this model and publish it as soon as I get some time.
Thank you. I will have at it and see if I can implement it in the VB script. I am a newbie and normally write in Google Apps Script. I have only been writing for a few months. Taking course on Codeacademy. I look forward to checking out your updated Rename Tool.
I am a Microsoft fan boy. I am glad yo are giving VB a chance. Let me know how it goes.
Hey Ejaz! This is brilliant! Indeed, a life-saver. I loved your tool… Just one small thing. It worked beautifully for a couple of times, but then I’m stuck now with an error popping out – Invalid Filename, Type Mismatch. Can you please help me understand why this error is popping?
Hi Henanksha,
I am glad you liked it. Try downloading a fresh copy.
if it doesn’t, I’d need more info to solve your problem.
Reach me at strugglingtoexcel@outlook.com
Regards,
Ejaz
Pingback: Rename a jpeg
Simply a life saver, now I can enjoy weekends with my family. I used to rename 300 images everyday manually.
That is the best comment I’ve ever received. My mission is to help people have better weekends!
You sir, have made my day!
First off, I would like to say thank you for making this pretty neat little tool. One bug I found though, it seems to give an error “Invalid Filename. VBA Error Description : File already exists” when you run the renamer on a batch of files that has a file without something to replace.
Like if I am replacing the word “test” and 9/10 files have the word “test” in it, but one file is just named “1.txt”, then it will give the error. If I remove the file “1.txt”, the error goes away.
I think that the problem lies with the program trying to write a new file with the same name to the folder whenever it encounters a file with no change. A line to skip any files that would not have a change should fix it.
Also, I wrote a macro for this that you may want to add. Basically, what it does is if you make a new sheet “Sheet1”, you can then put in a list things you want to rename in column “A” and then a list of what you want it to be renamed to in column “B”. You then run the macro and it will go down the sheet renaming them.
I had a table of 60 different things I needed to rename so I wrote it to not have to type everything in over and over. But, I imagine others may use it if they find themselves running the same name changes on multiple batches of files over time.
I will post it here when I clean it up a bit, if you would like.
I am glad you liked it. Thanks for letting me know about the bugs. There is another one too, files without extensions also throw errors. I’d look into it, and update the model.
Replacing multiple strings is a good idea, but I would leave that to my readers to build, considering my mission is to get people to start coding in vba themselves.
Thanks again.
You definitely accomplished your mission with me. I have not ever coded in VB and its been over a year since I coded in Java, but I had a blast doing it! I did not even know you could code VB into excel so easily like that.
Thanks again! Happy holidays!
You Sir, just made my day (and my day just started).
I have been looking for this kind of a tool for a long time. There are pretty much VBA codes around that renames the files, however this is the only one that can be used for Turkish characters (İ,Ç,Ğ,Ü etc.). Thank you so much..
I have a question: There are 6000 pictures to be renamed, but it only allows 502 of them. Is it possible somehow to extend it to 6000?
Hi. I am very happy that you like it. Thanks for bringing this up, I did not know this problem existed. I suspect it is an int vs long declaration problem. I will look into it, and update the model. Buzz me if I do not reply to you again in a week.
Hi again!! did you find a solution?
I haven’t yet. I went in a vacation and just got back. I will get a chance to look at it this weekend. Will keep you posted.
Ok. Thank you 🙂
Hi
I have updated the model. I tested it out with 900 files, and it works. Let me know what happend.
I even added some features like Hiding rows that are not used. Now, the model tests if you have formulas in the override column. Retains it, and extends it to the entire file list.
Test it out and let me know what you think.
Hi!! I tested the model with 6000 pics and still works. This version is much faster and more robust.
Thanks a lot 🙂
I am very happy that it worked for 6000 files. This is saved as a xls file. I suppose, if you save it as a xlsx file, you can rename even more files.
Sometimes it surprises me, how powerful excel is.
Since you expressed interest in renaming a lot of files, I went ahead and added in some code that displays the progress in the status bar.
Also, earlier, if the new name is the same name, it produces an error. I got rid of that. Also, I made the entire operation stop if an error is encountered, now only only that file gets skipped, and the loop goes on.
Then I added in some code to hide extra rows, to make the scrolling easier for you.
Hope it helped. I hope you’d come back here often. Cheers!
This tool really has saved me a ton of work, thank you.
I have been able to modify the VB code to use a VLOOKUP in another sheet to find the new file name that I want, would it be possible to add file moving capabilities with the new location to be identified with another VLOOKUP? And possibly restrict the files returned for renaming to only those of a certain extension?
I normally build PHP applications, so this is my first venture into working with VB with Excel. Any tips would be greatly appreciated.
I am sure the File System Object has file Moving/Copying methods. I probably have MSDN links to the FSO in the post somewhere. All you need to do is figure out how to specify arguments for the Move or Copy method.
Another tip I’d like to give you is: Please avoid using V/Hlookup functions. They are highly inefficient. Use the Index/Match combo. It is way more intuitive and super fast.
Let me know if you need more pointers to finish your task.
This is awesome and very handy tool! I use it a lot. Thank you for sharing, I appreciate it. -Sanjay
You are welcome!
Thank you for this! It has been really helpful! Is there a way to change the format of a date in the filename?
Such as – LST-02-S001-CSIF- 04-01-15 to LST-02-S001-CSIF-20150401
Well sure it can be done. You need to “parse” the file name and extract the dates from it.
In your particular case, I would use the split function, with “-” as the delimiter. Then I would use the last three array elements to reconstruct the date. Finally, ill remove the date in the original date, and append the date in any format I choose.
How many files do you have to deal with?
Around 20 per folder. I was trying to extract the date and add it to a new column, similar to your extension removal, but it proved to be a little complex for me, but since you confirmed that was way I’ll keep working at it. Thanks
You should also consider using the file system object to loop through folders also.
Try to write a udf to transform the filename, and add it to the overrides column.
I don’t know what a udf is. I learned a long time ago how to write code in college but I haven’t had to for a long time so and now I’m trying to remember and failing miserably. Thanks though I’ll keep at it
A udf, also known as a user defined function is something that takes an input, and returns an answer.
If you write a udf in excel VBA, you can use it as an Excel formula.
Dear Ejaz,
Your article was helping me indeed.
But I’ve made any edit for my personal use.
would you tell me how about make it to rename folder name, multiple folder ofcourse.
thanks in advance,
Nico
Hi Nick,
I am glad I was able to help.
The Folder Object has a collection call Files, and I use a For-Each loop on that. The Folder object has another collection called SubFolders. And all you have to do is implement a For Each Loop on that collection.
I recommend you look into the File System Object. It is a fascinating library. Let me know if you need more help.
Actually you have given me an idea for my next post. I can make a Folder Batch Renamer. Thank you for that.
Regards,
Ejaz Ahmed
Hi Ejaz,
Yeah. Meanwhile I read some stuff of FSO for folder renaming and still working on it. I’s still doing it with editing your module for folder renaming and combining it with some script.
I appreciate if in future I face any stuck I’ll contact you, indeed.
Thanks in advance,
Nico
Ejaz,
Thanks so much for making this up and letting me (everyone) have a copy. For me I am using it for my 5.4Tb video collection. I have just started playing with it, but would you be able to say if and how I could use a “Wild Card” in the “Replace What” section?
IE file list is as such
1978 – Title.avi
1979 – Title.mpg
1992 – Title.FLV
1999 – Title.MPK
I want to simply remove the whole part before Title but they are unique so???
You are welcome!
Well I have a over ride column in the model. You can use the string manipulation formulas like LEFT(), RIGHT() and MID()
I see that you will end up with the same name after you’ve removed the numbers. Think of a way to solve that.
Let me know if you need more details.
Hi guys, great tool! I am trying to organize my life, could you help me tweak your code a little bit?
I am building an index describing the files, so all i need is an extra column “Description” where I type the files characteristics, summary, etc… The code is giving error. And I’m afraid I might lose all my notes because of this.
I don’t understand what the problem is. Can you describe it in detail? Please write to contact@strugglingtoexcel.com
This is absolutely fantastic – thank you so much for the open manner in which you have shared this brilliant tool.
It is my pleasure! I am working on a tool that will import sub folders as well. I hope you will come back often. Thank you.
Hi Ejaz,
Thank you for the file and indeed great work. In addition to this have you been successful with import of sub-folders as well ?..
I am afraid I have not gotten around to working that into a blog post yet.
Thank you for your kind words.
Wow. This is some of the best excel stuff I’ve ever seen. This tool is nothing short of AWESOME!
You are too kind. Thank you. Glad you like it!
Ejaz,
As always, stellar work! I have a bit of a question that I’m hoping is an simple fix. If it’s not but you know where I can look to work it out for myself, I’d be super-appreciative.
I’m looking to batch rename a number of text files (pipe-delimited); the catch is, the new name depends on the data that if each file were imported into Excel would be at “G2”. Is there a simple way to adapt your code to allow for data within the imported file to provide data to rename the same file?? That is, how could I use the data at “G2” as the Suffix?
Thanks again!!
Sorry if this is a duplicate (got an error as I was posting the comment) :-\
For the same project, I’m wanting to replace the entire filename to (static string & data in G2).txt. Because the original filename contains an alphanumeric code that’s indirectly based on the date/timestamp of when the query was run, I can’t depend on a simple Find/Replace. Can the “Replace What” accept a wildcard (*.) to allow the entire filename to be changed while leaving the extension intact??
Thanks again
The prefix suffix and replace options can only benefit in certain scenarios as you rightly noted. To allow the user to add their logic, I’ve allowed for the override column. Feel free to implement any logic that fills up that column and you are good to go.
You can use excel formulas, or user defined function, or even separate macros to fill it out.
Then hit the rename button.
Sorry; I should have read more carefully. I just saw your response to Erick on Oct 03, 2015 at 6:17 PM. I think I can manage that after reading your directions.
Thanks!!
No problem at all. Happy to help.
Brilliant!! A masterpiece. I needed to rename files quickly, and after some searching came across this renamer, which by far is the best one yet and so simple to use.
Hi Ejaz,
Thank you so much, I was looking for this since 2 days and finally found here.
Great work, it is so simple and friendly to use, and renames bulk files in no time.
I really appreciate your work.
Thanks again.
Have a great life,
Regards,
Khalid
Hi this is great I am also looking for sub folders- did you ever work that out?
Thanks
Not yet I’m afraid. I have the recursive sub worked out, but still haven’t gotten around to integrating it into this tool.
Wow!! I am using your excel in my work to rename all files. It is so handy! thank you dear
I’m glad you like it. If I’ve saved you a lot of grief, do consider donating to keep my blog up and running.
Hey I am not able to download it gives me error of removing activeX content and when I download it downloads without macro buttons. Please advise.
Hi Sam. Let me look into this. I generally don’t use ActiveX controls. I try to use form controls as much as possible.
Do you see the other code modules in the downloaded file?
Hi EJAZ, this is very helpful, but could it be adjusted so that for each imported file (excels), it extracts also the content from a specific cell. The content is different, but the cell is always the same, for example A8 in each imported excel. For a year i have been doing manual work.. this will save me.
I need to have excels files in a folder renamed based on a certain cell inside each excel.
This can certainly be done. You can use the Workbook.Open method to open each file, and list down the value from the workbook in another column, close the file. Then finally run the rename macro.
Absolutely brilliant! Thank you very much, it works a treat!
Thank you for taking the time to appreciate my work. Please share with friends and colleagues!