2

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

VBA To Rename Files
'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
  • Unzip the file and open
  • Browse the folder which has your files
  •  
  • Enter ‘New File Name’ for each record
  • Click on ‘Rename Files’ button
Rename Files VBA
  • 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’
VBA Rename Excel Files

 

2
Leave a Reply

avatar
1 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
excelsirjiTrong Le Recent comment authors
newest oldest most voted
Trong Le
Guest
Trong Le

Hi

I downloaded the File Rename Tool and it did not run.

Asking fo the password to unprotect the sheet.

Please help.

Thanks,

Random Rows Selector Tool
FREE VBA UTILITIES
VBA Tool to Select Random Rows in Excel

Random Rows Selector is an MS Excel based tool which can be used to pick random or stratified samples from a set of records available in the Excel. The tool is fully dynamic, it can support any data format in Excel.

VBA Code to Count Cells by Color
VBA TRICKS
VBA Code to Count Cells by Color

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

VBA Code to Sum Cells by Color
VBA TRICKS
VBA Code to Sum Cells by Color

Recently some of our subscribers have requested us to share a VBA code that can sum the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to sum the cells with specific color and returns the total sum of the matching color cells

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn