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

Copy and rename multiple files

Objective

To copy and rename multiple files.

Approach

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.

Steps

  1. Declare the variables of source and target folder
  2. Check if both the source and target folder exist
  3. Copy all the excel files to target folder using CopyFile method
  4. Loop through each file in target folder
  5. Create a string for file name which contains prefix and suffix in the original file name
  6. Rename a single excel file by Name statement
  7. Move to next file to rename

Code

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.

 

move and rename file vba



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.

 

copy and rename multiple files


Note

  1. With this approach we are keeping the original files unchanged.
  2. We can also rename any specific type of files

Reference

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

Post you may like

Rename folder or directory in VBA Excel | debugvba.com