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."
		        ' 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
		    wscript.Echo "The file """ & file.Name & _
			         """ could not be renamed."
		End If
	        ' Close the workbook without saving.
		wb.Close False
	    End If
        End If
    ' 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."
        ' Feedback stating number of files renamed.
        If filesRenamed = 1 Then
            wscript.Echo filesRenamed & " file renamed successfully."
            wscript.Echo filesRenamed & " files renamed successfully."
        End If
    End If


    ' Message stating file path does not exist.
    wscript.Echo "File path does not exist."

End If