To copy file from one folder to another folder using CopyFile “Method” .
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.
- Initialize the variables for source and target folder path
- Check if source and target folders (or directory ) exist
- Use ” Copyfile ” method to copy only .pptx type of files to target folder
- Exit the subroutine if source or destination folder does not exist
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.
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.
We can copy any specific type (*.pptx or *.xlsx) or all type (*.*) of file using this approach.
There are many methods applicable for FileSystemObject, which we can use to handle tasks related to files and folders.
Post you may like