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.
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
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:
