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.
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