Rename the Multiple files
VBA

Rename Multiple File on one click

In this article you will learn how to rename the multiple 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 the Multiple files

 

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

Add Reference

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:

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com