List Files in a Folder and Rename them – Batch Renamer

Batch Renamer Scareenshot
Batch Renamer Screen Shot

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

  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 : 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’

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

 

67 Comments

  1. Sajjad

    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?

      • Sajjad

        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.

  2. WallyG

    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.

      • WallyG

        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.

  3. Henanksha

    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?

  4. UDAI SINGH

    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!

  5. Henry Mayes

    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.

      • Henry Mayes

        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!

  6. fasih

    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.

      • fasih

        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.

          • fasih

            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.

          • fasih

            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!

  7. Woody

    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.

  8. Sanjay

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

  9. Zach Thompson

    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?

      • Zach Thompson

        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.

          • Zach Thompson

            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.

  10. Nico

    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

    • Ejaz

      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

      • Nico

        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

  11. Erick

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

    • Ejaz

      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.

  12. Vladmir

    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.

  13. GK

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

    • Ejaz

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

        • Ejaz

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

          Thank you for your kind words.

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

    • Ejaz

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

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

      • Ejaz

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

        • Ejaz

          No problem at all. Happy to help.

  16. Baber Beg

    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.

  17. Khalid Rehman

    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

  18. Karen

    Hi this is great I am also looking for sub folders- did you ever work that out?

    Thanks

    • Ejaz

      Not yet I’m afraid. I have the recursive sub worked out, but still haven’t gotten around to integrating it into this tool.

  19. Ibna

    Wow!! I am using your excel in my work to rename all files. It is so handy! thank you dear

    • Ejaz

      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.

  20. Sam

    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.

    • Ejaz

      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?

      • Mary

        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.

        • Ejaz

          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.

  21. Linda

    Absolutely brilliant! Thank you very much, it works a treat!

    • Ejaz

      Thank you for taking the time to appreciate my work. Please share with friends and colleagues!

Comments are closed