How to Automate File Renaming in Bulk Using Excel VBA

First, you need to open Excel.

Press ALT + F11 to open the Visual Basic for Applications editor.

In the VBA editor, you can insert a new module by going to Insert > Module. This will create a new module where you can write your code.

Now, you can use the following script which uses VBA to rename the files in the folder:

Declaring Variables:

JavaScript

Dim folderPath As String
Dim fileName As String
Dim newFileName As String
Dim fileNumber As Integer

Here, variables to hold the folder path, the current file name, the new file name, and the file number are declared.

Specifying the Folder Path:

JavaScript

folderPath = "C:\YourFolderPath\"

This line is where you specify the directory that contains the files you want to rename. You need to replace `"C:\YourFolderPath\"` with the path to your target folder.

Initializing the File Number:

JavaScript

fileNumber = 1

`fileNumber` is initialized to 1, indicating the starting number that will be appended to the file names.

Ensuring the Folder Path is Correct:

JavaScript

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

This line checks if the folder path ends with a backslash `\` and if not, it adds one. This is necessary because the backslash is used in file paths to separate directory levels.

Retrieving the First File in the Directory:

JavaScript

fileName = Dir(folderPath & "*.*")

The `Dir` function is used to return a string representing the name of the first file in the directory that matches the string pattern `*.*`, where `*` is a wildcard that represents any sequence of characters. This means it will match all files regardless of their file extension.

Loop to Rename Files:

JavaScript

Do While fileName <> ""
    '...
Loop

This is the beginning of a loop that continues until there are no more files in the directory (i.e., until `Dir` returns an empty string).

Constructing the New File Name:

JavaScript

newFileName = "File " & fileNumber & Mid(fileName, InStrRev(fileName, "."))

Within the loop, a new file name is constructed by concatenating the string `"File "`, the current `fileNumber`, and the file's extension. The `Mid`, `InStrRev`, and `"."` are used to extract the file extension from the original file name.

Renaming the File:

JavaScript

Name folderPath & fileName As folderPath & newFileName

This line uses the `Name` statement to rename the file. It specifies the old file name and path, and the new file name and path.

Moving to the Next File:

JavaScript

fileName = Dir()

This line uses the `Dir` function without arguments to retrieve the next file in the directory.

Incrementing the File Number:

JavaScript

fileNumber = fileNumber + 1

This increments the `fileNumber` by 1 for each iteration of the loop, so that each file gets a unique number.

Message Box to Indicate Completion:

JavaScript

MsgBox "Files have been renamed!", vbInformation

After the loop has finished (all files have been renamed), a message box is displayed to inform you that the renaming process is complete. The `vbInformation` constant gives the message box an information icon.

Full Code:

JavaScript

Sub RenameFilesInFolder()
    ' Declare variables
    Dim folderPath As String
    Dim fileName As String
    Dim newFileName As String
    Dim fileNumber As Integer

    ' Specify the folder path where the files are located (end the path with a backslash "\")
    folderPath = "C:\YourFolderPath\"  ' <-- .="" 1="" a="" add="" are="" backslash.="" change="" check="" ends="" file="" filenumber="1" files="" folder="" folderpath="" if="" initialize="" located="" not="" number="" path="" right="" the="" this="" to="" where="" with="" your=""> "\" Then folderPath = folderPath + "\"

    ' Get the first file in the directory
    fileName = Dir(folderPath & "*.*")  ' <-- all="" asterisk="" change="" do="" e.g.="" extensions="" file="" filename="" files="" for="" limit="" loop="" means="" only="" rename="" specific="" start="" text="" the="" to="" txt="" types.="" while=""> ""
        ' Construct new file name
        newFileName = "File " & fileNumber & Mid(fileName, InStrRev(fileName, "."))
        
        ' Rename the file
        Name folderPath & fileName As folderPath & newFileName
        
        ' Move to the next file
        fileName = Dir()
        
        ' Increment the file number
        fileNumber = fileNumber + 1
    Loop

    ' Inform the user that renaming has been completed
    MsgBox "Files have been renamed!", vbInformation
End Sub

Your feedback and suggestion is very valuable for us.
So please comment your thoughts and request code below