Batch File Renaming with VBScript
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. All the files at the specified location are then processed. If the file is an Excel file, it is opened and the value from the chosen cell is extracted. A check is then made to see if the value is valid for a file name. If it is, the file is renamed, if the new name is different to the old one. Finally, feedback is given as to the number of files renamed.
' File system object. Dim fso Set fso = WScript.CreateObject("Scripting.FileSystemObject") ' File path. Dim path path = "C:\Demo\" ' Folder. Dim folder ' Valid file name. Dim validFileName Set validFileName = CreateObject("vbscript.regexp") validFileName.Pattern = "[\\/:\*\?""<>\|]" ' Check if the file path exists. If fso.FolderExists(path) Then ' Workbook. Dim wb ' Cell containing new file name. Dim cellForFileName cellForFileName = "A1" ' Old file name. Dim oldFileName oldFileName = "" ' New file name. Dim newFileName newFileName = "" ' Renamed file count. Dim filesRenamed filesRenamed = 0 ' Set up Excel. Dim objExcel Set objExcel = CreateObject("Excel.Application") objExcel.Application.ScreenUpdating = False objExcel.Application.EnableEvents = False ' Assign the folder at the path. Set folder = Fso.GetFolder(path) ' Process the files in the folder. For Each file In folder.Files ' Check if the file is an Excel file. If InStr(file.Name, ".xlsx") Then ' Assign the workbook to a variable. Set wb = objExcel.Workbooks.Open(file) ' Check if there is a value in the cell for the new file name. If wb.Worksheets(1).Range(cellForFileName).Value <> "" Then ' Assign cell value for new file name to a variable. newFileName = wb.Worksheets(1).Range(cellForFileName).Value ' Close the workbook without saving. wb.Close False ' Check if the new name is valid. If Not validFileName.Test(newFileName) Then ' Check if the file needs to be renamed. if file.Name = newFileName & ".xlsx" Then ' Message stating file doesn't need renaming. wscript.Echo "The file """ & file.Name & _ """ doesn't need renaming." Else ' Extract old file name. oldFileName = file.Name ' Rename file. fso.MoveFile file, path & newFileName & ".xlsx" ' Message stating file has been renamed. wscript.Echo "The file """ & oldFileName & _ """ has been renamed to """ & _ newFileName & """.xlsx." ' Increment the file count. filesRenamed = filesRenamed + 1 End If Else wscript.Echo "The file """ & file.Name & _ """ could not be renamed." End If Else ' Close the workbook without saving. wb.Close False End If End If Next ' Re-enable previously disabled Excel features. objExcel.Application.EnableEvents = True objExcel.Application.ScreenUpdating = True ' Feedback on renamed files. If filesRenamed = 0 Then ' Message stating no files were renamed. wscript.Echo "No files were renamed." Else ' Feedback stating number of files renamed. If filesRenamed = 1 Then wscript.Echo filesRenamed & " file renamed successfully." Else wscript.Echo filesRenamed & " files renamed successfully." End If End If Else ' Message stating file path does not exist. wscript.Echo "File path does not exist." End If