File Search and Renaming with Visual Basic

If it is necessary to rename a batch of files, where both the existing and new file names exist in an Excel spreadsheet, Visual Basic 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.

Existing File Name New File Name Notes
OldFileName1 NewFileName1
OldFileName2
OldFileName3 NewFileName3
OldFileName4 NewFileName4

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.

Imports System.IO
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

Public Class BatchFileSearchRename

    Public Shared Sub Main()

        ' Excel.
        Dim xlApp As Excel.Application = New Excel.Application

        ' File names workbook.
        Dim pathNamesDoc As String
        pathNamesDoc = "C:\Demo\Rename\Documents to rename.xlsx"
        Dim xlNamesWorkbook As Excel.Workbook = Nothing
        Dim xlNamesWorksheet As Excel.Worksheet = Nothing

        ' Path to documents to rename.
        Dim pathDocsRename As String
        pathDocsRename = "C:\Demo\"

        ' Check if the file and folder paths exist.
        If File.Exists(pathNamesDoc) And Directory.Exists(pathDocsRename) Then

            Try

                ' Open the file names workbook and assign to a variable.
                xlNamesWorkbook = xlApp.Workbooks.Open(pathNamesDoc)

            Catch ex As Exception

                ' Message stating that workbook can't be opened.
                Console.WriteLine("Unable to open file names workbook.")

                ' Quit Excel and exit.
                xlApp.Quit()
                End

            End Try

            ' Select the first sheet.
            xlNamesWorksheet = xlNamesWorkbook.Sheets(1)

            ' Last row and file name variables.
            Dim lastRow As Long
            Dim currentFileName As String
            Dim newFileName As String
            Dim fileExtension As String = ".docx"

            ' Renamed file count.
            Dim filesRenamed As Integer
            filesRenamed = 0

            ' Find the last row used.
            lastRow = xlNamesWorksheet.UsedRange.Rows.Count

            ' Check if there are any files to rename.
            If lastRow <= 1 Then

                ' Message stating no files to rename.
                Console.WriteLine("There are no files to rename.")

            Else

                ' Process the rows, from second row to the last used.
                For Row = 2 To lastRow

                    ' Extract the current and new file name.
                    currentFileName = xlNamesWorksheet.Cells(Row, 1).Value
                    newFileName = xlNamesWorksheet.Cells(Row, 2).Value

                    ' Check if the file exists and a new name has been provided.
                    If File.Exists(pathDocsRename & currentFileName & fileExtension) And
                        Not String.IsNullOrEmpty(newFileName) Then

                        ' Check if the new file name is valid.
                        Dim isValid As Boolean =
                            newFileName.IndexOfAny(Path.GetInvalidFileNameChars()) < 0

                        If isValid Then

                            Try

                                ' Rename file.
                                System.IO.File.Move(
                                    pathDocsRename & currentFileName & fileExtension,
                                    pathDocsRename & newFileName & fileExtension)

                                ' Add message to column 3 stating file has been renamed.
                                xlNamesWorksheet.Cells(Row, 3).Value =
                                    "File has been renamed."

                                ' Increment the files renamed count.
                                filesRenamed += 1

                            Catch ex As Exception

                                ' Add message to column 3 stating file can't be renamed.
                                xlNamesWorksheet.Cells(Row, 3).Value =
                                    "File could not be renamed."

                            End Try

                        Else

                            ' Add message in column 3 stating no new valid name provided.
                            xlNamesWorksheet.Cells(Row, 3).Value =
                                "No new valid file name provided."

                        End If

                    Else

                        ' Check if new file name has been provided.
                        If String.IsNullOrEmpty(newFileName) Then

                            ' Add message in column 3 stating no new file name provided.
                            xlNamesWorksheet.Cells(Row, 3).Value =
                                "No new valid file name provided."

                            ' Check if file has already been renamed.
                        ElseIf File.Exists(
                            pathDocsRename & newFileName & fileExtension) Then

                            ' Add message in column 3 stating file already renamed.
                            xlNamesWorksheet.Cells(Row, 3).Value =
                                "File has already been renamed."

                        Else

                            ' Add message in column 3 stating file does not exist.
                            xlNamesWorksheet.Cells(Row, 3).Value =
                                "File does not exist."

                        End If

                    End If

                Next

                Try

                    ' Save the file names workbook.
                    xlNamesWorkbook.Save()

                Catch ex As Exception

                    ' Message stating file names workbook could not be saved.
                    Console.WriteLine("The file names workbook could not be saved.")

                End Try

                ' Feedback on renamed files.
                If filesRenamed = 0 Then

                    Console.WriteLine("No files were renamed.")

                ElseIf filesRenamed = 1 Then

                    Console.WriteLine(filesRenamed & " file renamed successfully.")

                Else

                    Console.WriteLine(filesRenamed & " files renamed successfully.")

                End If

            End If

            ' Close the file names workbook and quit Excel.
            xlNamesWorkbook.Close(False)
            xlApp.UserControl = True
            xlApp.Quit()
            Marshal.ReleaseComObject(xlNamesWorksheet)
            Marshal.ReleaseComObject(xlNamesWorkbook)
            Marshal.ReleaseComObject(xlApp)

        Else

            ' Check if its the file or folder path that doesn't exist.
            If Not File.Exists(pathNamesDoc) Then

                ' Error message if file names workbook doesn't exist.
                Console.WriteLine("The document containing the file names does not exist.")

            Else

                ' Error if folder path for documents to be renamed doesn't exist.
                Console.WriteLine("The location of the files to rename does not exist.")

            End If

        End If

        ' Force console window to stay open until a key is pressed.
        Console.ReadKey()

    End Sub

End Class