How to Rename Files | Excel | VBA

Here is another help code and tool for programmers to rename files.

You can use this tool for renaming all files available in a selected folder. Just browse the folder and hit ‘Rename Files’ button

'This function loops through all the files listed in the sheet and
 'rename them as per details provided by user
 Public Sub RenameFiles()
     '
     'Variable declaration
     Dim lCounter As Long
     Dim lInnerCounter As Long
     Dim bHasError As Boolean
     Dim strPath As String
     '
     'Clear Status field
     Sheet1.Range("E7:E" & Sheet1.Rows.Count).ClearContents
     '
     'Check if New File Name is not left blank
     bHasError = False
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         If Trim(Sheet1.Range("D" & lCounter).Value) = "" Then
             Sheet1.Range("E" & lCounter).Value = "New File Name cannot be left blank"
             bHasError = True
         End If
     Next
     If bHasError = True Then
         MsgBox "There are few validation errors." & vbNewLine & vbNewLine & "Please check column E (Status) for details.", vbInformation
         Exit Sub
     End If
     '
     'Check if there are duplicate file names
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         For lInnerCounter = lCounter + 1 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
             If Trim(LCase(Sheet1.Range("D" & lCounter).Value)) = Trim(LCase(Sheet1.Range("D" & lInnerCounter).Value)) Then
                 Sheet1.Range("E" & lCounter).Value = "Duplicate File Name"
                 bHasError = True
                 Exit For
             End If
         Next
     Next
     If bHasError = True Then
         MsgBox "There are few validation errors." & vbNewLine & vbNewLine & "Please check column E (Status) for details.", vbInformation
         Exit Sub
     End If
     '
     'Store path in a variable and add slash to it
     strPath = Sheet1.Range("C3").Value
     If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "" Then
         strPath = strPath & ""
     End If
     'Rename files
     For lCounter = 7 To Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Row
         On Error GoTo Error_FileRename
         '
         'Use Name function to rename file
         Name strPath & Sheet1.Range("C" & lCounter).Value As strPath & Sheet1.Range("D" & lCounter).Value
         '
         'Update status column as success
         Sheet1.Range("E" & lCounter).Value = "Success"
         '
         On Error GoTo 0
     Next
     '
     'Show confirmation to user
     MsgBox "Done", vbInformation
     '
     Exit Sub
     '
 'Error Handler if file rename produces error
 Error_FileRename:
     MsgBox "An error occurred while renaming file '" & Sheet1.Range("C" & lCounter).Value & "'." & vbNewLine & vbNewLine & "Error: " & Err.Description, vbCritical
     Sheet1.Range("E" & lCounter).Value = "Failed"
     Exit Sub
 End Sub

How to use this tool:

• Save the following attachment in your system

File Rename ToolDownload

• Unzip the file and open
• Browse the folder which has your files

• Enter ‘New File Name’ for each record

• Click on ‘Rename Files’ button

• Done, all the files will be renamed and you will receive a confirmation message.

Note: Just incase if the tool is not able to rename few files, you will get those details in Status column.

• To clear the data, click on ‘Clear Data’

Hope you liked this article !!

Looking for Ready-made Tool for Duplicate Files Finder? Click below!!

Subscribe our blog for new amazing excel tricks.

Click to below for some more interesting tricks and learning:

• VBA Code to Re-Link MS Access linked Tables 
• VBA Code to check if File Exists in Folder 
• VBA Code to Browse File 
• VBA Code to Count Color Cells 
• Find Duplicates Values in Excel 

Please leave your valuable comments in Comments section: