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.
- Initialize the variables for source data folder
- Check if source folder exists or not.If source folder does not exist , then exit the code.
- Loop through each file in the source folder
- 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
- Use “Name” statement to change the file name
- 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 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.
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.
- We can rename any other specific type of files using similar approach
- We can also keep the original file intact, and save the output file in a separate folder
Post you may like