Copy file from one folder to another by copyfile method in VBA Excel

Copy file from one location to another vba excel

Objective

To copy file from one folder to another folder using CopyFile “Method” .

Approach

In this VBA Excel automation, we are copying the files from source to target folder. First we have initialized source and target folder, then we check if source and target folder exist or not.If they exists then we copy all the .pptx files from source to target folder.

Steps

  1. Initialize the variables for source and target folder path
  2. Check if source and target folders (or directory ) exist
  3. Use ” Copyfile ” method to copy only .pptx type of files to target folder
  4. Exit the subroutine if source or destination folder does not exist


Code

 

Sub CopyAllMyFiles()

    Dim objFileSystem As Object
    Dim SourceFolder As String
    Dim TargetFolder As String
    Dim MyFile As Object
    
    'Set the file system object
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
  
    
    'Path of the folder where files are locted
    SourceFolder = "C:\Users\myusername\Desktop\macro\Final\New\Source_Folder\"
    TargetFolder = "C:\Users\myusername\Desktop\macro\Final\New\Target_Folder\"
          
          
    'Check if the folder already exists
    If Dir(SourceFolder, vbDirectory) <> "" And Dir(TargetFolder, vbDirectory) <> "" Then
      
    'Copy the files from source to target folder
    objFileSystem.CopyFile SourceFolder & "\" & "*.pptx", TargetFolder

   
    MsgBox "All files moved to target folder"
    Else
    MsgBox "Either source or target folder does not exist"
End If

    
End Sub


Before execution of code, we had 2 PowerPoint files in source folder, while target folder was empty.

 

Copy file from one location to another vba excel


After executing the VBA code, both the PowerPoint files have been copied to target folder.Now we can see in the image below that both source and target folder have 2 files.

Copy file from one location to another vba excel


We can copy any specific type (*.pptx or *.xlsx) or all type (*.*) of file using this approach.

Note

There are many methods applicable for FileSystemObject, which we can use to handle tasks related to files and folders.


Reference

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

Post you may like

How to copy files from one folder to another using copy command in VBA Excel