How to rename multiple folders using VBA Excel

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