Rename Multiple File on one click

In this article you will learn how to Rename Multiple File files available in a folder. In the previous post I have explained you how we can get the files information in a excel sheet. We will use the same method to get the current file names of given folder. Once we have the file names in the excel sheet then we will give new name in another excel column and will click on Rename Files button.

Rename Multiple File

We will use the file system object to create this.

To use the file system object you need to add “Microsoft Scripting Runtime” Reference. To add this Reference go to Visual basic editor > Tools > References > Microsoft Scripting Runtime Rename Multiple File

Below is the code to get current file names form the folder.

Option Explicit

Sub Get_Files_Information()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(sh.Range("H1").Value)

Dim last_raw As Integer


For Each f In fo.Files
last_raw = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
sh.Range("A" & last_raw).Value = f.Name
Next


MsgBox "Done"


End Sub

Below is the code to rename the files (From Column A to Column B in above image)

Sub Rename_Files()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(“Sheet1”)

Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Dim new_name As String

Set fo = fso.GetFolder(sh.Range(“H1”).Value)

For Each f In fo.Files
new_name = Application.VLookup(f.Name, sh.Range(“A:B”), 2, 0)
f.Name = new_name
Next

MsgBox “Done”

End Sub

Click here to download this Excel workbook.

Watch the step by step tutorial:

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert