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.

1. Declare the variables for source and target folder path
2. Check if source and target folders or directory exist
3. Invoke the command prompt, we are passing multiple commands in single line
4. Here we are using “cd” command to reach source folder or directory
5. Use ” Copy ” command to copy only .pptx type of files to target folder or directory
6. Exit command is used to exit from command prompt console
7. 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.

 

 

After execution of code, we can see that both the PowerPoint files have been copied to target folder.

 

 

Note

There are many methods 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

Rename multiple files in a folder or directory by VBA Excel | debugvba.com