Objective
To rename multiple folders using VBA Excel.
Approach
Here in the code below we are trying to rename multiple folders, which exist at a path. We loop through each of the folder and then add a string called “Prefix_” at the beginning and a string “_Suffix” at the end of a name of the folder. We can also add date or any other string in the folder name as per requirement. We shall see the same in the later part of this post.
In the code below we have provided the path in variable “SourceFolderPath”, at this path multiple folders exist. We can see the same in the image below. Here we have used FilSystemObject object to access the folder structure. We have used the SubFolder property to access the folders which exist in parent folder. We have changed the name of the folder using Name statement.
In the image below we can see how the folder structure looked like before running the VBA Excel code.We shall loop through each of these folders using VBA Excel code.
Code
Sub RenameMultipleFolders() Dim ObjFileSystem As Object Dim MyParentFolder As Object, MySubFolder As Object, SubFolderList As Object Dim SubFolderName As String, SourceFolderPath As String Set ObjFileSystem = CreateObject("Scripting.FileSystemObject") 'This is the path where all sub folders exist SourceFolderPath = "C:\Users\yourusername\Desktop\Test" Set MyParentFolder = ObjFileSystem.GetFolder(SourceFolderPath) 'Get list of all the sub folders in source folder Set SubFolderList = MyParentFolder.SubFolders 'Loop through each subfolder For Each MySubFolder In SubFolderList 'Get only name of sub folder without path SubFolderName = ObjFileSystem.GetBaseName(MySubFolder) OldFolderName = SourceFolderPath & "\" & SubFolderName NewFolderName = SourceFolderPath & "\" & "Prefix_" & SubFolderName & "_Suffix" 'Rename folders Name OldFolderName As NewFolderName Next End Sub
Once we execute the VBA code, a prefix and suffix are added to name of each folder, we can see the same in the image below.
Note
We need to remember that all the files which are present in these folders, should be closed before running VBA code ,otherwise code will throw error, since folder cant be renamed if any file inside these folder is open.
Reference
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/subfolders-property
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/name-statement
Post you may like
Convert or Save As multiple word (.docx) files to PDF in VBA Excel