Batch File Search and Renaming with VBScript

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

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 a valid 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.

' File system object.
Dim fso
Set fso = WScript.CreateObject("Scripting.FileSystemObject")

' File names workbook and sheet.
Dim pathNamesDoc
pathNamesDoc = "C:\Demo\Rename\Documents to rename.xlsx"
Dim wbNamesDoc
Dim wsNamesDoc

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

' Valid file name.
Dim validFileName
Set validFileName = CreateObject("vbscript.regexp")
validFileName.Pattern = "[\\/:\*\?""<>\|]"

' Check if the file and folder paths exist.
If fso.FileExists(pathNamesDoc) And fso.FolderExists(pathDocsRename) Then

    ' Set up Excel.
    Dim objExcel
    Set objExcel = CreateObject("Excel.Application")

    ' Open the file names workbook and assign to a variable.
    Set wbNamesDoc = objExcel.Workbooks.Open(pathNamesDoc)

    ' Assign the first sheet to a variable.
    Set wsNamesDoc = wbNamesDoc.Worksheets(1)

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

    ' Renamed file count.
    Dim filesRenamed
    filesRenamed = 0

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

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

        ' Message stating no files to rename. 
        wscript.Echo "There are no files to rename." 

        ' Close file names workbook without saving. 
        wbNamesDoc.Close False

    Else

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

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

            ' Check if the file exists and a valid new name has been provided. 
            If fso.FileExists(pathDocsRename & currentFileName & fileExtension) And _ 
               Len(newFileName) > 0 And _ 
               Not validFileName.Test(newFileName) And _ 
               Not fso.FileExists(pathDocsRename & newFileName & fileExtension) Then

                ' Rename the file.
                fso.MoveFile pathDocsRename & currentFileName & fileExtension, _
                             pathDocsRename & newFileName & fileExtension
                
                ' Add message to column 3 stating file has been renamed.
                wsNamesDoc.Cells(Row, 3).Value = _
                    "File has been renamed."
                
                ' Increment the files renamed count.
                filesRenamed = filesRenamed + 1

            Else

                ' Check if new file name has been provided.
                If Len(newFileName) = 0 Then
                
                    ' Add message in column 3 stating no new file name provided.
                    wsNamesDoc.Cells(Row, 3).Value = _
                        "No new file name provided."
                
                ElseIf validFileName.Test(newFileName) Then
                
                    ' Add message in column 3 stating invalid new file name.
                    wsNamesDoc.Cells(Row, 3).Value = _
                        "The new file name is invalid."
                
                ElseIf fso.FileExists(pathDocsRename & newFileName & fileExtension) Then
                
                    ' Add message is column 3 stating file has already been renamed.
                    wsNamesDoc.Cells(Row, 3).Value = _
                        "File has already been renamed."
                        
                Else
                
                    ' Add message to column 3 stating file does not exist.
                    wsNamesDoc.Cells(Row, 3).Value = _
                        "File does not exist."
                        
                End If

            End If

        Next

        ' Feedback on renamed files.
        If filesRenamed = 0 Then

            wscript.Echo "No files were renamed."

        ElseIf filesRenamed = 1 Then

            wscript.Echo filesRenamed & " file renamed successfully."

        Else

            wscript.Echo filesRenamed & " files renamed successfully."

        End If

        ' Close and save the file names workbook.
        wbNamesDoc.Close True

    End If

Else

    If Not fso.FileExists(pathNamesDoc) Then

        ' Error message if file names workbook doesn't exist.
        wscript.Echo "The document containing the file names does not exist."

    Else

        ' Error if folder path for documents to be renamed doesn't exist.
        wscript.Echo "The location of the files to rename does not exist."

    End If

End If