WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA File Copy

VBA File Copy

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

FileCopy syntax

  • 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

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

You can download this VBA File Copy Excel Template here – VBA File Copy Excel Template

Example #1

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

VBA FileCopy Example 1

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

VBA FileCopy Example 1-1

Ok, let’s write the code for it.

Open FileCopy function inside the Sub procedure.

Code:

Sub FileCopy_Example1()

FileCopy

End Sub

VBA FileCopy Example 1-2

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

Code:

Sub FileCopy_Example1()

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

End Sub

VBA File Copy Example 1-1

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub FileCopy_Example1()

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

End Sub

Provide File Name

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

Paste Destination Path

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 Files\VBA\April Files\Sales April 2019.xlsx”

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

Destination Path

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.

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

Code #2

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 the file name and excel 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 Function

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

Error

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.

Recommended Articles

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 –

  • INSTRREV Function in VBA
  • VBA Chr
  • File System Object in VBA
  • VBA Delete File
3 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA File Copy Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More