To copy and rename multiple files.
In this VBA Excel automation, we are copying all the excel (.xlsx) files from a source folder to output folder using CopyFile method , and then we are renaming the files which we have copied to output folder by using Name statement . We loop through each file in source folder to rename them.
- Declare the variables of source and target folder
- Check if both the source and target folder exist
- Copy all the excel files to target folder using CopyFile method
- Loop through each file in target folder
- Create a string for file name which contains prefix and suffix in the original file name
- Rename a single excel file by Name statement
- Move to next file to rename
Sub CopyAllMyFiles() 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 located SourceFolder = "C:\Users\yourusername\Desktop\New\Source_data\" TargetFolder = "C:\Users\yourusername\Desktop\New\Target_data\" Set objFileSystem = CreateObject("Scripting.FileSystemObject") 'Check if source and target folder exists If objFileSystem.FolderExists(SourceFolder) = True And objFileSystem.FolderExists(TargetFolder) = True Then 'Copy the files to target folder objFileSystem.CopyFile SourceFolder & "\" & "*.xlsx", TargetFolder 'Looping through each .xlsx file in the target folder For Each OriginalFile In objFileSystem.getfolder(TargetFolder).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 "Either source or target folder does not exist" End If End Sub
Before execution of the code, we can see that source data folder has 2 files, while target folder was empty.
Now after execution of code, we can see that source folder has 2 files, at the same time renamed copies of both the source file have been created in target folder.
- With this approach we are keeping the original files unchanged.
- We can also rename any specific type of files
Post you may like