Batch File Renaming with Visual Basic

Below is an example of how Microsoft Excel files can be renamed in a batch, based on the contents of a particular cell within each file.

Firstly, the file path is set and a check is carried out to make sure that the path exists. This is followed by a check to make sure that there are files to process at the desired location. Each file is then processed one by one. All files without a ‘.xlsx’ extension are ignored. If the value in the specified cell of the first sheet in each individual file is populated and it contains only valid characters then the file is renamed and a message is displayed confirming each name change, otherwise a message is displayed stating that a particular file name could not be changed. Finally, a count of the number of files that have been renamed is displayed.

Imports System.IO
Imports Microsoft.Office.Interop

Public Class BatchFileRenaming

    Public Shared Sub Main()

        ' File path.
        Dim filePath As String = "C:\Demo"

        ' File extension.
        Dim fileExtension As String

        ' Check if the file path exists.
        If Directory.Exists(filePath) Then

            ' Return the names of the files at the specified path.
            Dim dirFiles As String() = Directory.GetFiles(filePath)

            ' Check if there are any files at the path.
            If dirFiles.Length = 0 Then

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

            Else

                ' Excel file variables.
                Dim xlApp As Excel.Application = New Excel.Application
                Dim xlWorkbook As Excel.Workbook
                Dim xlWorksheet As Excel.Worksheet
                Dim newFileName As String

                ' Renamed file count.
                Dim filesRenamed As Integer = 0

                ' Process the files at the path.
                For Each dirFile As String In dirFiles

                    ' Extract the file extension from the name.
                    fileExtension = Path.GetExtension(dirFile)

                    ' Check if the file is an Excel file.
                    If fileExtension = ".xlsx" And Not dirFile.Contains("~") Then

                        ' Open the Excel file and select sheet 1.
                        xlWorkbook = xlApp.Workbooks.Open(dirFile)
                        xlWorksheet = xlWorkbook.Sheets(1)

                        ' Check if there is a value in cell A1.
                        If Not String.IsNullOrEmpty(xlWorksheet.Cells(1, 1).Value) Then

                            ' Construct new file name from value in cell A1.
                            newFileName = xlWorksheet.Cells(1, 1).Value.ToString() + ".xlsx"

                            ' Check if the new file name is valid.
                            Dim isValid As Boolean = Not String.IsNullOrEmpty(newFileName) And
                                newFileName.IndexOfAny(Path.GetInvalidFileNameChars()) < 0 And
                                Not File.Exists(Path.Combine(filePath, newFileName))

                            If isValid Then

                                Try

                                    ' Close the workbook.
                                    xlWorkbook.Close()

                                    ' Rename the file.
                                    System.IO.File.Move(dirFile, filePath + "\" + newFileName)

                                    ' Message confirming file has been renamed.
                                    Console.WriteLine("The file ""{0}"" has been renamed to " +
                                                      """{1}"".", dirFile.ToString(),
                                                      newFileName)

                                    ' Increment the files renamed count.
                                    filesRenamed += 1

                                Catch ex As Exception

                                    ' Message confirming the file could not be renamed.
                                    Console.WriteLine("The file ""{0}"" could not be renamed.",
                                                      dirFile.ToString())

                                End Try

                            Else

                                ' Close the workbook.
                                xlWorkbook.Close()

                                ' Message confirming the file could not be renamed.
                                Console.WriteLine("The file ""{0}"" could not be renamed.",
                                                  dirFile.ToString())

                            End If

                        Else

                            ' Close the workbook.
                            xlWorkbook.Close()

                            ' Message confirming the file could not be renamed.
                            Console.WriteLine("The file ""{0}"" could not be renamed.",
                                              dirFile.ToString())

                        End If

                    End If

                Next

                ' Message stating the number of files renamed.
                If filesRenamed = 0 Then

                    Console.WriteLine("No files have been renamed.")

                ElseIf filesRenamed = 1 Then

                    Console.WriteLine("{0} file has been renamed.", filesRenamed)

                Else

                    Console.WriteLine("{0} files have been renamed.", filesRenamed)

                End If

            End If

        Else

            ' Display a message stating file path does not exist.
            Console.WriteLine("File path does not exist.")

        End If

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

    End Sub

End Class