Visual Basic gives us access to parts of the Windows environment; therefore, there are lots of file-based activities we can achieve with VBA. One of these is to use VBA to copy a file.
In this post, we look at 5 examples of how to achieve this.
Copy a file VBA
In this post, we use the basic FileCopy method. For more advanced options, we can use the File Systems Options.
Example 1: Copy a file
The example below copies Example File.xlsx from C:\Users\marks\Documents\ to C:\Users\marks\Documents\Test\.
Sub VBACopyFile() FileCopy "C:\Users\marks\Documents\Example File.xlsx", _ "C:\Users\marks\Documents\Test\Example File.xlsx" End Sub
Note: In this example, the file name is unchanged.
Example 2: Copy a file using variables
In Example 1, the file names were included within the FileCopy statement. However, they can also be provided as variables.
In the code below:
- The variables are declared (i.e., created)
- Values are assigned to the variables
- The variables are used in the FileCopy statement
Sub VBACopyFileVariables() 'Declare variables Dim copyFromFile As String Dim copyToFile As String 'Assign values to variables copyFromFile = "C:\Users\marks\Documents\Example File.xlsx" copyToFile = "C:\Users\marks\Documents\Example File Copied.xlsx" 'Use variables in the FileCopy statement FileCopy copyFromFile, copyToFile End Sub
Note: In this example, the file is not copied to a new location, but is renamed from Example File.xlsx to Example File Copied.xlsx
Example 3: Copy a file based on cell values
In this example, we copy a file using file paths contained in cell values.
Look at the screenshot below. Cell C2 contains the current file path and Cell C4 contains the path to which the file is to be copied.
We can run the following macro to rename a file using these cell values.
Sub VBACopyFileSheetNames() FileCopy ActiveSheet.Range("C2"), _ ActiveSheet.Range("C4") End Sub
Note: In this example, the file is copied to a new folder and also renamed from Example File.xlsx to Example File Copied.xlsx
Example 4: Check file existence before VBA copy
The FileCopy command will happily overwrite a file without showing any errors. Therefore, it is a good idea to check if a file already exists before copying over it.
The code below checks for the existence of a file in the target location. If a file exists, a message box with a Yes/No options appear. Clicking No exists the macro.
Sub VBACheckTargetFileCopyFile() 'Declare variables Dim copyFromFile As String Dim copyToFile As String Dim msgBoxAnswer As Long 'Assign variables to file names copyFromFile = "C:\Users\marks\Documents\Example File.xlsx" copyToFile = "C:\Users\marks\Documents\Test\Example File Copied.xlsx" 'Check if the file already exists If Dir(copyToFile) <> "" Then 'Display message box and capture answer msgBoxAnswer = MsgBox(Prompt:="File already exists in that location." & _ vbNewLine & "Do you wish to overwrite it?", Buttons:=vbYesNo, _ Title:="Copying file") 'If answer is No, the exist the macro If msgBoxAnswer = vbNo Then 'Exit the macro Exit Sub End If End If 'File does not exist or Yes clicked so continue FileCopy copyFromFile, copyToFile End Sub
Example 5: Avoiding errors when copying files
Copying files can trigger errors; the errors are outlined in the section below. The following code builds on Example 4 and also provides an error message box if any errors occur.
Sub VBAAdvancedCopyFile() 'Declare variables Dim copyFromFile As String Dim copyToFile As String Dim msgBoxAnswer As Long 'Assign variables to file names copyFromFile = "C:\Users\marks\Documents\Example File.xlsx" copyToFile = "C:\Users\marks\Documents\Test\Example File Copied.xlsx" On Error Resume Next 'Check if the file already exists If Dir(copyToFile) <> "" Then 'Display message box and capture answer msgBoxAnswer = MsgBox(Prompt:="File already exists in that location." & _ vbNewLine & "Do you wish to overwrite it?", Buttons:=vbYesNo, _ Title:="Copying file") 'If answer is No, the exist the macro If msgBoxAnswer = vbNo Then 'Exit the macro Exit Sub End If End If 'File does not exist or Yes clicked so continue FileCopy copyFromFile, copyToFile 'Display message if error occured If Err.Number <> 0 Then MsgBox Prompt:="Unable to copy file", Buttons:=vbOK, _ Title:="Copy file error" End If 'Turn error checking back on On Error GoTo 0 End Sub
Possible errors
Copying a file during automation can cause errors. Below are some of the common errors from the VBA copy file process.
Copying a file that does not exist triggers an error: Run-time error’53’: File not found.
Copying a file to a file location that is locked (i.e., another user has the file open) triggers an error: Run-time error ’70’: Permission denied
Notes on copying files
To finish off this post, there are just a few things to make you aware of:
- We have used Excel workbooks in the examples, but we can use any file type.
- FileCopy cannot copy complete folders, only files.
- The FileCopy command is core Visual Basic code. We find this code in other applications windows applications such as Word and PowerPoint.
- Check out this post for more example codes to manage files and folders: VBA code to copy, move, delete and manage files.
Also, check the VBA code library for hundreds of reusable code snippets.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
