Excel VBA to Rename Files in a Folder (with Buttons!)

Sharing is caring!

Renaming multiple files manually can be time-consuming, especially when dealing with large batches of files. Excel VBA provides a powerful way to automate this task by allowing users to rename files in a selected folder based on a predefined list in an Excel sheet.

In this guide, we will cover how to:

• Create an interface in Excel for selecting a folder
• Extract and list file names in Excel
• Rename files based on user input
• Ensure the process runs smoothly with error handling

By the end of this article, you will have a fully functional VBA script that can rename multiple files efficiently.

Setting Up the Excel Interface

To make this process user-friendly, we will create a simple Excel interface with buttons and a field for folder selection. Follow these steps:

1. Insert a cell (e.g., B1) to store the selected folder path.
2. Add three buttons:
   • “Select Folder” (to choose the target folder)
   • “Get File Names” (to list file names in Column A)
   • “Rename Files” (to execute the renaming operation)
3. Reserve Column A for existing file names and Column B for new names.
4. Assign VBA macros to each button to automate the process.

Adding Buttons in Excel

• Go to Developer > Insert > Form Controls > Button
• Place three buttons and assign macros (explained in the next section).

VBA Code for Automating File Renaming

We will use VBA to handle the following tasks:

1. Selecting a Folder
2. Listing All File Names in Column A
3. Renaming Files Based on User Input in Column B

1. VBA Code to Select a Folder

This macro allows users to pick a folder and displays its path in cell B1.

Sub SelectFolder()
    Dim folderPath As FileDialog
    Set folderPath = Application.FileDialog(msoFileDialogFolderPicker)
    
    If folderPath.Show = -1 Then
        ThisWorkbook.Sheets("Sheet1").Range("B1").Value = folderPath.SelectedItems(1) & "\"
    End If
End Sub

2. VBA Code to Get File Names from Folder

This script reads all file names from the selected folder and lists them in Column A.

Sub GetFileNames()
    Dim folderPath As String
    Dim fileName As String
    Dim ws As Worksheet
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear previous content in both columns
    ws.Range("A:B").ClearContents
    
    folderPath = ws.Range("B1").Value
    
    If folderPath = "" Then
        MsgBox "Please select a folder first.", vbExclamation, "Error"
        Exit Sub
    End If
    
    fileName = Dir(folderPath, vbNormal) ' Only normal files (skip folders/system files)
    i = 2 ' Start from row 2
    Do While fileName <> ""
        ws.Cells(i, 1).Value = fileName
        fileName = Dir()
        i = i + 1
    Loop
    
    MsgBox "File names have been listed.", vbInformation, "Success"
End Sub

3. VBA Code to Rename Files

This macro renames files based on Column A (current names) and Column B (new names).

Sub RenameFiles()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim oldFileName As String, newFileName As String
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    folderPath = ws.Range("B1").Value
    
    If folderPath = "" Then
        MsgBox "Please select a folder first.", vbExclamation, "Error"
        Exit Sub
    End If
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim renameCount As Long
    renameCount = 0

    For i = 2 To lastRow
        oldFileName = folderPath & ws.Cells(i, 1).Value
        newFileName = folderPath & ws.Cells(i, 2).Value
        
        If ws.Cells(i, 2).Value = "" Then
            ' Skip if no new name provided
            GoTo NextFile
        End If
        
        If Dir(oldFileName) <> "" Then
            Name oldFileName As newFileName
            renameCount = renameCount + 1
        Else
            MsgBox "File not found: " & oldFileName, vbExclamation, "Error"
        End If
        
NextFile:
    Next i
    
    MsgBox renameCount & " files have been renamed.", vbInformation, "Success"
End Sub

How It Works

Assigning Macros to Buttons

1. Right-click each button
2. Select Assign Macro
3. Choose the corresponding macro:
   • Select Folder → SelectFolder
   • Get File Names → GetFileNames
   • Rename Files → RenameFiles

Important Considerations

• Ensure new file names in Column B include valid extensions (.txt, .xlsx, .jpg, etc.).
• Column A must contain exact matches of current file names.
• Always test on a sample folder before renaming important files.
• If a file in Column A does not exist, an error message will be displayed.
• File names must be unique within the same folder.

Final Thoughts

By using Excel VBA, you can efficiently rename multiple files in a folder with just a few clicks. This method eliminates manual renaming errors and saves time. With proper setup, users can manage bulk file renaming with ease, ensuring a smooth and error-free process.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.