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 BatchRenamer V1.01.xls from Dropbox.
  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, a full spreadsheet application will be locked down with more layers of protection.

Advertisements

57 thoughts on “List Files in a Folder and Rename them – Batch Renamer

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

    Like

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

    Liked by 1 person

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

    Like

    • 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

      Like

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

        Like

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

        Like

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

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

    • 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

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

    • 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

      • 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

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

    • 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

      • 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

          • 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

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

    • 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

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

    • 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

            • 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

                • 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

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

    • 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

  11. Pingback: Rename a jpeg

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

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

    • 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

      • 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

  14. Pingback: Using the EURO symbol in filenames via an excel generated RENAME statement in a batch file ...!

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

        • 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s