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

Objective

To copy file from one folder to another folder by executing copy command in command prompt.

Approach

In this VBA Excel automation, we are copying the files from source to target folder. Here we are executing copy “command” in command prompt

Steps

In this approach we invoke the command prompt shell using VBA.

Declare the variables for source and target folder path
Check if source and target folders or directory exist
Invoke the command prompt, we are passing multiple commands in single line
Here we are using “cd” command to reach source folder or directory
Use ” Copy ” command to copy only .pptx type of files to target folder or directory
Exit command is used to exit from command prompt console
Exit the subroutine if source or destination folder does not exist

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

Code

Sub CopyAllFiles()
Dim SourceFolder As String
Dim TargetFolder As String

   
'Path of the folder where files are located
SourceFolder = "C:\Users\myusername\Desktop\macro\Final\New\Source_Folder\"
TargetFolder = "C:\Users\myusername\Desktop\macro\Final\New\Target_Folder\"
          
'Check if both source and target folder already exists
If Dir(SourceFolder, vbDirectory) <> "" And Dir(TargetFolder, vbDirectory) <> "" Then
'We can use *.* or any specific file extension as well to move any type of file
Call Shell("cmd.exe /S /C" & "cd " & SourceFolder & " && " & "copy *.pptx " & TargetFolder & "&&" & " Exit")
MsgBox "All files copied 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 is empty.