List Files in a Folder and Rename them – Batch Renamer

Batch Renamer Scareenshot

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
Posted In

61 Comments

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

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

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

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

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 )

w

Connecting to %s