VBA File Copy

Excel VBA File Copy

VBA “File Copy” as the name copies the file from one location to mentioned another location using VBA Function. For this, we need to mention the current file path and destination file path. Ok, let us look at the syntax of the FileCopy function in VBA.

• Source: This is nothing but from where we need to copy the file. We need to mention the fully qualified folder path.
• Destination: This is the destination folder where we need to paste the copied file.

Examples of File Copy Function in Excel VBA

Below are the examples of how to copy files using VBA Code.

Example #1

Let’s start with a simple example. I have a file named as “Sales April 2019” in the folder. Below is the image of the same i.e. “Source”.

From the above location, I want to copy this file and paste to a different folder. Below is the image of the same i.e. “Destination Source”.

Ok, let’s write the code for it.

Open FileCopy function inside the Sub procedure.

Code:

Sub FileCopy_Example1()

FileCopy

End Sub

Now for the first argument, we need to mention the file path where our current is in.

Code:

Sub FileCopy_Example1()

FileCopy “D:\My Files\VBA\April Files

End Sub

After mentioning the folder path we need to mention the file with file extension as well. So mention the file name by putting a backslash (\).

Code:

Sub FileCopy_Example1()

FileCopy "D:\My Files\VBA\April Files\Sales April 2019.xlsx",

End Sub

Now in the second argument mention where we need to paste the copied file.

Code:

Sub FileCopy_Example1()

FileCopy "D:\My Files\VBA\April Files\Sales April 2019.xlsx", "D:\My Files\VBA\Destination Folder\Sales April 2019.xlsx"

End Sub

One thing we need to do hereafter mentioning the folder path at the end we need to mention the file name as well in the destination argument.

Now run the code using F5 key or manually then, it will copy the file from below location to a destination location.

“D:\My Files\VBA\April Files\Sales April 2019.xlsx”

“D:\My Files\VBA\Destination Folder\Sales April 2019.xlsx”

Example #2 – Use Variables to Store Source Path and Destination Path.

In the previous example, we have supplied the source path and destination path directly to the formula. But this is not the best practice to continue, so let’s store them in variables.

For an example look at the below code.

Code:

Sub FileCopy_Example2()

Dim SourcePath As String
Dim DestinationPath As String

SourcePath = "D:\My Files\VBA\April Files\Sales April 2019.xlsx"
DestinationPath = "D:\My Files\VBA\Destination Folder\Sales April 2019.xlsx"

FileCopy SourcePath, DestinationPath

End Sub

Let me explain the code in detail for you.

First I have declared two variables.

Dim SourcePath As String

Dim DestinationPath As String

Then for the first variable, I have assigned the folder path from where it has to copy the file and the file name along with its file extension.

SourcePath = "D:\My Files\VBA\April Files\Sales April 2019.xlsx"

For the second variable similarly, I have assigned the destination folder path with file name and extension.

DestinationPath = "D:\My Files\VBA\Destination Folder\Sales April 2019.xlsx"

Then for the formula FileCopy, I have supplied these variables instead of lengthy folder path strings.

FileCopy SourcePath, DestinationPath

Like this, we can use variables to store the paths and use them efficiently.

Example #3 – Error with File Copy Formula

Sometimes File Copy formula encounters an error of “Permission Denied”.

The reason why we get this error because when the copying file opened and if you try to copy the above error comes, so always close the file and execute the code.

Recommended Articles

This has been a guide to VBA File Copy. Here we discuss how to copy files from one directory to another using FileCopy Function in VBA along with examples and downloadable templates. Below you can find some useful excel VBA articles –

• Refresh All Pivot Table in the Same Worksheet using VBA
• INSTRREV Function in VBA
• Exit VBA For Loop
• UDF in Excel VBA
• VBA Chr Function
• File System Object in VBA
• File Dialog Box in VBA
• GetOpenFilename Method VBA
• How to Delete File using VBA?