Advertisements

List Files in a Folder and Rename them – Batch Renamer



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

  1. 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 : https://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
    
  2. 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

  1. 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 : https://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

  1. 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.
  2. 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 : https://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 : https://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’

  1. 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 : https://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
    
  2. Finally we assign all these macros to suitable buttons.

Instructions to use the Application:

  1. Download the file from the section below.
  2. Click the ‘ImportFiles‘ Button to browse for the folder that has the files you would like to rename.
  3. 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.
  4. 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.
  5. 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.

Batch Renamer Scareenshot

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

Download

 

Advertisements
Categories: ApplicationsTags: , , , , , , , , , , , , ,

65 comments

  1. 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

    Liked by 1 person

  2. 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?

    Like

  3. If you are interested in doing it yourself, I can give you some pointers.

    Like

  4. Pls go ahead, give me some pointers.
    I would like to try out myself

    Like

  5. 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.

    Like

  6. 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….

    Liked by 1 person

  7. 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.

    Like

  8. 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.

    Like

  9. I am a Microsoft fan boy. I am glad yo are giving VB a chance. Let me know how it goes.

    Like

  10. 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?

    Like

  11. 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

    Like

  12. Simply a life saver, now I can enjoy weekends with my family. I used to rename 300 images everyday manually.

    Liked by 1 person

  13. That is the best comment I’ve ever received. My mission is to help people have better weekends!

    You sir, have made my day!

    Like

  14. 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.

    Liked by 1 person

  15. 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.

    Like

  16. 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!

    Liked by 1 person

  17. You Sir, just made my day (and my day just started).

    Like

  18. 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?

    Liked by 1 person

  19. 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.

    Like

  20. Hi again!! did you find a solution?

    Like

  21. 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.

    Like

  22. Ok. Thank you 🙂

    Like

  23. 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.

    Like

  24. Hi!! I tested the model with 6000 pics and still works. This version is much faster and more robust.
    Thanks a lot 🙂

    Liked by 1 person

  25. 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!

    Like

  26. 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.

    Like

  27. 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.

    Like

  28. This is awesome and very handy tool! I use it a lot. Thank you for sharing, I appreciate it. -Sanjay

    Liked by 1 person

  29. 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

    Liked by 1 person

  30. 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?

    Like

  31. 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

    Liked by 1 person

  32. 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.

    Like

  33. 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

    Liked by 1 person

  34. 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.

    Like

  35. 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

    Liked by 1 person

  36. 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

    Like

  37. 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

    Liked by 1 person

  38. 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???

    Liked by 1 person

  39. 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.

    Like

  40. 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.

    Like

  41. I don’t understand what the problem is. Can you describe it in detail? Please write to contact@strugglingtoexcel.com

    Like

  42. This is absolutely fantastic – thank you so much for the open manner in which you have shared this brilliant tool.

    Liked by 1 person

  43. 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.

    Like

  44. 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 ?..

    Like

  45. I am afraid I have not gotten around to working that into a blog post yet.

    Thank you for your kind words.

    Like

  46. Wow. This is some of the best excel stuff I’ve ever seen. This tool is nothing short of AWESOME!

    Liked by 1 person

  47. You are too kind. Thank you. Glad you like it!

    Like

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: