Rename multiple files in a folder or directory by VBA Excel | debugvba.com

VBA rename multiple files folder

Objective

To rename multiple files in a folder using VBA excel.

Approach

In this VBA Excel automation, we are renaming multiple excel files in the source folder by adding the prefix and suffix in names of all the source files. Original files will be replaced with renamed files . Here we loop through each file in the source folder, then we change the name of excel files one by one.We are using Name statement to rename the files.


Steps

  1. Initialize the variables for source data folder
  2. Check if source folder exists or not.If source folder does not exist , then exit the code.
  3. Loop through each file in the source folder
  4. If selected file is an excel file, then (1) Get the path of the selected excel file (2) Create the string for name which we want to assign to original file
  5. Use “Name” statement to change the file name
  6. Move to next file and continue the same process until all files are renamed.

 

Code

 

Sub RenameMyFiles()

    Dim objFileSystem As Object
    Dim SourceFolder As String
    Dim OriginalFile, RenamedFile As String
    Dim MyPrefix, MySuffix As String
    
    MyPrefix = "MyPrefix_"
    MySuffix = "_MySuffix"

    'Path of the folder where files are locted
    SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source_Data\"
    
          
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
    'Check if source and target folder exists
    If objFileSystem.FolderExists(SourceFolder) = True Then
    
    'Looping through each .xlsx file in the source folder
     For Each OriginalFile In objFileSystem.getfolder(SourceFolder).Files
    
    'Check if selected file is an excel file
     If InStr(OriginalFile, ".xlsx") Then
     
     MyFileLocation = objFileSystem.GetParentFolderName(OriginalFile)
     RenamedFile = MyFileLocation & "\" & MyPrefix & Replace(Dir(OriginalFile), ".xlsx", "") & MySuffix & ".xlsx"
     
     'Rename original file
     Name OriginalFile As RenamedFile
     
     End If
     Next OriginalFile
     
     Else
        MsgBox "Source folder does not exist"
     End If
     
End Sub

 

 

Before executing the code, we had 2 excel files in source folder. We shall rename both the files here.

 

VBA rename mutiple files folder

 

Once the VBA code is executed, we can see the prefix (“MyPrefix”) and suffix (“MySuffix”) has been added to file names of both the files.

 

 

VBA rename multiple files folder

Note

  1. We can rename any other specific type of files using similar approach
  2. We can also keep the original file intact, and save the output file in a separate folder


Reference

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

Post you may like

Copy and rename multiple files using VBA Excel | debugvba.com