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