Excel VBA FileCopy Function
File Copy is an inbuilt vba function used to copy the file from one location to another mentioned location. To use this function, we need to mention the current file path and destination file path.
Ok, let us look at the syntax of the FileCopy function.
- 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.
Below are the examples of how to copy files using VBA CodeUsing VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
Let’s start with a simple example. I have a file named “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 it 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.
Now for the first argument, we need to mention the file path where our currency is in.
FileCopy “D:My FilesVBAApril Files
After mentioning the folder path, we need to mention the file with a file extension as well. So mention the file name by putting a backslash ().
Sub FileCopy_Example1() FileCopy "D:My FilesVBAApril FilesSales April 2019.xlsx", End Sub
Now in the second argument, mention where we need to paste the copied file.
Sub FileCopy_Example1() FileCopy "D:My FilesVBAApril FilesSales April 2019.xlsx", "D:My FilesVBADestination FolderSales 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 the F5 key or manually. Then, it will copy the file from the below location to a destination location.
“D:My FilesVBAApril FilesSales April 2019.xlsx”
“D:My FilesVBADestination FolderSales 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 example, look at the below code.
Sub FileCopy_Example2() Dim SourcePath As String Dim DestinationPath As String SourcePath = "D:My FilesVBAApril FilesSales April 2019.xlsx" DestinationPath = "D:My FilesVBADestination FolderSales 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 FilesVBAApril FilesSales April 2019.xlsx"
For the second variable, similarly, I have assigned the destination folder path with the file name and excel extension.Excel Extension.Excel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.
DestinationPath = "D:My FilesVBADestination FolderSales 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 Function
Sometimes File Copy function encounters an error of “Permission Denied.”
The reason why we get this error because when the copying file is opened and if you try to copy the above error comes, so always close the file and execute the code.
This has been a guide to VBA File Copy. Here we discuss how to copy excel files from one directory to another using the FileCopy function along with examples. Below you can find some useful excel VBA articles –