To rename a folder using VBA Excel.
In this VBA Excel automation, we are renaming a folder which contains the files. However, the code mentioned below is independent of the case whether the target folder contains the files or not. In this case we first check if the target folder already exists or not. If folder does not exist, then we rename it.
- Initialize the file system objects to change the folders
- Initialize the variable for old folder path and renamed target folder path
- Check if there is already a folder with the same name as that of the renamed folder
- If folder does not exist, then rename the folder by Rename statement
- If folder already exist, then display the message that folder already exists at same path
Sub RenameMyFolders() Dim OldFolderName, NewFolderName As String Dim objFileSystem As Object Set objFileSystem = CreateObject("Scripting.FileSystemObject") 'Path for old folder and renamed folder OldFolderName = "C:\Users\yourusername\Desktop\New\Source_Data\" NewFolderName = "C:\Users\yourusername\Desktop\New\Changed_Source_Data\" 'Check if renamed folder already exist If objFileSystem.FolderExists(NewFolderName) = False Then 'Rename the original folder Name OldFolderName As NewFolderName MsgBox "Target Folder is renamed" Else MsgBox "Target Folder already exists with same name" End If End Sub
Before executing the VBA code, we had a folder called “Source_Data” as shown in the image below.
After executing the VBA code, target folder [i.e. Source_Data] will be renamed as “Changed_Source_Data“. New folder name was initialized in the code itself.
After execution of VBA code,a message box will be displayed as shown below.
If the there is already a folder which has same name as that of our target folder, then a message box will be shown.See the image below. To understand this ,let’s assume that there is already a folder with name as “Changed_Source_Data” at same path, then folder renaming will not happen.
- We can rename multiple folders using similar approach
- We need to ensure that there is no folder with the same name
Post you may like