Copy Folders , Subfolder in VBA Excel – debugvba.com

copy folder sub folder vba excel

Objective

To copy the folder ,subfolder and all the contained files using VBA Excel.

Approach

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 –

  1. Using CopyFolder Method of file system object
  2. 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 sourcedestination, [ 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.

Code

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.

 

copy folder sub folder vba excel

 

Before executing the VBA code , target folder was blank, we can see that in the image below.

 

copy folder sub folder vba excel

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.

 

copy folder sub folder vba excel

 

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

Code

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.

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/copyfolder-method

Post you may like

Copy Formula down to last row using FillDown method in VBA Excel