How to Copy File in VBA

We will introduce how we can copy a file using VBA with examples.

Copy File in VBA

File copy is a VBA function that allows us to copy files from one location to any location we desire. This feature is very easy to use.

We need to mark the current file path and the destination file path.

The syntax is as shown below.

# vba
FileSysObj.CopyFile src, dst, [ overwrite ]

The src is the supplier. We can copy a file from the source.

It is a spot from where we want to copy the file. We have to declare a fully eligible folder path.

The dst is the target folder; we will paste the copied file into this folder. Overwrite is optional.

Let’s take an example and copy a file name testFile from the folder. The code for this function is shown below.

Sub TestFile()

Dim FileSysObj As Object

Set FileSysObj = CreateObject("Scripting.FileSystemObject")

Call FileSysObj.CopyFile("D:\Test\testFile.xlsx", "D:\Test\Dst\", True)

End Sub

We will need an object of the class FileSystemObject. Let’s form the object of the class as shown below.

Set FileSysObj = CreateObject("Scripting.FileSystemObject")

Now we will apply the CopyFile method as shown below.

Call FileSysObj.CopyFile("D:\Test\testFile.xlsx", "D:\Test\Dst\", True)

Output before running code:

Output after running code:

We have three parameters: Source, Destination, and Overwrite. As source and destination are the same, we will use overwrite.

We have to fix it to true or false.

We will set it to True in our case, which says that our original file is overwritten. This is what we should have done but let’s see what happens if we fix overwrite to False.

This can be done only by changing the code, as shown below.

Call FileSysObj.CopyFile("D:\Test\testFile.xlsx", "D:\Test\Dst\", False)

Output:

As a result, an error will occur. We can also rename a file while copying. It is just like copying a file.

We only have to customize the destination path to a different name, as shown below in the code.

Sub TestFile()
Dim FileSysObj As Object

Set FileSysObj = CreateObject("Scripting.FileSystemObject")

Call FileSysObj.CopyFile("D:\Test\testFile.xlsx", "D:\Test\Dst\testFile2.xlsx", True)
End Sub

Output: