Rename folder or directory in VBA Excel | debugvba.com

rename folder directory using vba excel

Objective

To rename a folder using VBA Excel.

Approach

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.

Steps

  1. Initialize the file system objects to change the folders
  2. Initialize the variable for old folder path and renamed target folder path
  3. Check if there is already a folder with the same name as that of the renamed folder
  4. If folder does not exist, then rename the folder by Rename statement
  5. If folder already exist, then display the message that folder already exists at same path

Code

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.

 

rename folder directory using vba excel

 

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.

 

 

rename folder directory using vba excel

After execution of VBA code,a message box will be displayed as shown below.

 

rename folder directory using vba excel

 

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.

 

 

rename folder directory using vba excel

 

Notes

  1. We can rename multiple folders using similar approach
  2. We need to ensure that there is no folder with the same name

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/name-statement

Post you may like

Remove special characters using replace function in VBA Excel – debugvba.com