Batch File Search and Renaming with VBA

If it is necessary to rename a batch of files, where both the existing and new file names exist in an Excel spreadsheet, VBA can be used to automate the renaming process.

In the example below, a spreadsheet called “Documents to rename.xlsx” contains the file name information. Column ‘A’ contains the existing file name, without the file extension, column ‘B’ contains the new file name, if available, without the extension, and column ‘C’ is for notes to be added during the renaming process.

First of all, the path to the above document, as well as to the files to be renamed, is set and a check is made to see if they exist. If they do exist, the document containing the file names is opened and the file name information on the first sheet is processed. The last row used in column ‘A’ is obtained and all the rows, from the second to the last are handled one by one. The current and new file names are extracted from columns ‘A’ and ‘B’, using their numerical references, 1 and 2. A check is made to see if the file exists with the current name and if it does the file gets renamed to the new name, providing there is one available. Feedback is added in Column 3 as to whether the file was renamed or not, or, if it had already been renamed. Overall feedback is also given as to the total number of files renamed.

' File names workbook.
Dim pathNamesDoc As String
pathNamesDoc = "C:\Demo\Rename\Documents to rename.xlsx"
Dim wbNamesDoc As Workbook
 
' Path to documents to rename.
Dim pathDocsRename As String
pathDocsRename = "C:\Demo\"
 
' Check if the file and folder paths exist.
If Dir(pathNamesDoc) <> "" And Dir(pathDocsRename, vbDirectory) <> "" Then
 
    ' Open the file names workbook and assign to a variable.
    Set wbNamesDoc = Workbooks.Open(Filename:=pathNamesDoc)
     
    ' Last row and file name variables.
    Dim lastRow As Long
    Dim currentFileName As String
    Dim newFileName As String
     
    ' Renamed file count.
    Dim filesRenamed As Integer
    filesRenamed = 0
 
    ' First sheet.
    With wbNamesDoc.Sheets(1)
         
        ' Find the last row used in column A.
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         
        ' Check if there are any files to rename.
        If lastRow <= 1 Then
         
            ' Message stating no files to rename.
            MsgBox ("There are no files to rename.")
           
            ' Close file names workbook without saving and exit.
            wbNamesDoc.Close SaveChanges:=False
            Exit Sub
         
        End If
         
        ' Process the rows, from second row to the last used.
        For Row = 2 To lastRow
         
            ' Extract the current and new file name.
            currentFileName = .Cells(Row, 1).Value
            newFileName = .Cells(Row, 2).Value
             
            ' Check if the file exists.
            If Dir(pathDocsRename & currentFileName & ".docx") <> "" Then
             
                ' Check if a new file name has been provided.
                If newFileName <> "" Then
                 
                    ' Rename file.
                    Name pathDocsRename & currentFileName & ".docx" _
                    As pathDocsRename & newFileName & ".docx"
                 
                    ' Add message to column three stating file has been renamed.
                    .Cells(Row, 3).Value = "File has been renamed."
                     
                    ' Increment the files renamed count.
                    filesRenamed = filesRenamed + 1
                 
                Else
                 
                    ' Add message in column three stating no new name provided.
                    .Cells(Row, 3).Value = "No new file name provided."
                 
                End If
             
            Else
             
                ' Check if file has already been renamed.
                If Dir(pathDocsRename & newFileName & ".docx") <> "" Then
                     
                    ' Add message in column three stating file has already been renamed.
                    .Cells(Row, 3).Value = "File has already been renamed."
                 
                Else
                 
                    ' Add message in column three stating file does not exist.
                    .Cells(Row, 3).Value = "File does not exist."
                 
                End If
             
            End If
         
        Next
         
    End With
     
    ' Save and close file names workbook.
    wbNamesDoc.Close SaveChanges:=True
     
    ' Feedback on renamed files.
    If filesRenamed = 0 Then
     
        MsgBox ("No files were renamed.")
     
    ElseIf filesRenamed = 1 Then
     
        MsgBox (filesRenamed & " file renamed successfully.")
     
    Else
     
        MsgBox (filesRenamed & " files renamed successfully.")
     
    End If

Else
     
    If Dir(pathNamesDoc) = "" Then
     
        ' Error message if file names workbook doesn't exist.
        MsgBox ("The document containing the file names does not exist.")
     
    Else
     
        ' Error if folder path for documents to be renamed doesn't exist.
        MsgBox ("The location of the files to rename does not exist.")
     
    End If
 
End If