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

VBA rename multiple files folder


To rename multiple files in a folder using VBA excel.


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.


  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.




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
        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


  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


Post you may like

Copy and rename multiple files using VBA Excel |