To copy the folder ,subfolder and all the contained files using VBA Excel.
In this VBA excel automation ,we have showcased 2 ways to copy folder and its subfolders, which includes files in the source folders. Below mentioned are the approaches –
- Using CopyFolder Method of file system object
- By using xcopy command in VBA excel
1. CopyFolder Method
This is a method of FileSystemObject object. FileSystemObject object is part of Scripting Library. Here first we shall create an object of FileSystemObject, then with the help of CopyFolder method we can copy folder and sub folders.
If the target folder does not exist, then below shown code will create the target folder itself. We can test this by deleting the target folder.
Syntax for this method is shown below –
object.CopyFolder source, destination, [ overwrite ]
Here we shall set the value of OverWrite as True or False. If overwrite value is set as True, and if we copy a file/folder from source folder which already exist in target folder, then no error will be thrown, because we have allowed overwriting by this option . But if the value of OverWrite is set as False, and we still try to copy a preexisting file/folder to target folder, then it will throw an error.
Sub CopyFolderByVbaMethod() Dim MyFileSysObj As Object Set MyFileSysObj = CreateObject("Scripting.FileSystemObject") Dim SourceFolder As String, TargetFolder As String SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source" TargetFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Target" 'Copy the folder and sub folder MyFileSysObj.CopyFolder SourceFolder, TargetFolder, True End Sub
Before executing the VBA code, we had 1 folder and 2 sample files, we aim to copy these files and folder from source to target folder.
Before executing the VBA code , target folder was blank, we can see that in the image below.
Once we execute the VBA code, 1 folder and 2 sample files which were present in the source folder are copied into target folder.We can see that in the image below.
(2) Xcopy Command in command prompt
Here we invoke the command prompt in VBA excel and execute the “xcopy” command, which is like “Copy” command, but “xcopy” command can copy sub folders in a folder. “Copy” command can not copy the sub folders. Like CopyFolder method, if the target folder does not exist, then xcopy command will create the target folder itself.
Sub CopyFolderByCommandPrompt() Dim SourceFolder As String, TargetFolder As String SourceFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Source" TargetFolder = "C:\Users\yourusername\Desktop\macro\Final\New\Target" 'Copy the folder and sub folder Call Shell("cmd.exe /c xcopy /y " & SourceFolder & " " & TargetFolder & " /E /i ") End Sub
We can apply any of the above methods to copy folder, subfolder and files contained within them.
Post you may like