How to copy file with VBA in Excel (10 examples)

VBA in Excel gives us access to parts of the Windows environment. Therefore, there are many file-based activities we can achieve with VBA Macrs in Excel. One common method is using VBA to copy files from one folder to another.

In this post, we look at 10 examples of how to achieve this.

Table of Contents

• BasicSyntax
• Examples
• Example 1: Copy a file
• Example 2: Copy a file using variables
• Example 3: Copy a file based on cell values
• Example 4: Check file existence before VBA copy
• Example 5: Avoiding errors when copying files
• Example 6: Selecting file to copy
• Example 7: Copy file with function
• Example 8: Moving vs copying
• Example 9: Alternative method – FSO
• Possible errors
• Notes on copying files

Basic Syntax

In this post, we use the basic FileCopy method. For more advanced options, we can use the File Systems Options.

FileCopy is a VBA function that accepts 2 arguments.

FileCopy source, destination

• source – Text string specifying the file to be copied. The argument is required.
• destination – Text string specifying the target file name. This argument is required.

Examples

In this section, we work through 10 examples to demonstrate how to use the FileCopy statement.

Example 1: Copy file

The example below copies Example File.xlsx from the C:\Users\marks\Documents\ folder to the C:\Users\marks\Documents\Test\ folder.

Sub VBACopyFile()

FileCopy "C:\Users\marks\Documents\Example File.xlsx", _
    "C:\Users\marks\Documents\Test\Example File.xlsx"

End Sub

Change the file paths to make this code work in your environment.

Note: In this example, the file location has changed, but 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 C3 contains the current file path and Cell C5 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("C3"), _
    ActiveSheet.Range("C5")

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 overwrite an existing file without showing any errors. Therefore, it is a good idea to check if a file already exists before copying 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 appears. 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

Example 6: Select the file to copy

Sometimes we want the ability to select a file, so let’s take a look at how we can achieve this.

In this example:

• A user selects a file path in a cell on the worksheet, this will be the destination (Cell C3)
• When the macro runs, a user selects the file using the File Open dialog box, this file will be the source.
• The source file is copied to the destination file path.

Sub VBASelectCopyFile()

'Declare variables
Dim dialogBox As FileDialog
Dim copyFromFile As String
Dim copyToFile As String
Dim msgBoxAnswer As Long

'Turn off error checking
On Error Resume Next

'set dialog box as variable
Set dialogBox = Application.FileDialog(msoFileDialogOpen)

'Set the dialog box properties
dialogBox.AllowMultiSelect = False
dialogBox.Title = "Select a file"

'Show the dialog box and output full file name
If dialogBox.Show = -1 Then
    copyFromFile = dialogBox.SelectedItems(1)
Else
    Exit Sub
End If

'Get destination from cell selection
copyToFile = ActiveCell.Value

'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

Example 7: Copy file with function

With VBA we can create worksheet functions. Therefore it is possible to copy a file each time a formula executes.

The following is a simple VBA function to copy a file.

Function FunctionCopyFiles(copyFromFile, copyToFile)

'Turn off error checking
On Error Resume Next

'Use function arguments to copy the file
FileCopy copyFromFile, copyToFile

'Return the value for the function
If Err.Number = 0 Then
 FunctionCopyFiles = "File Copied"
Else
 FunctionCopyFiles = "File Not Copied"
End If

'Turn error checking back on
On Error GoTo 0

End Function

The screenshot below shows an example of using the function:

The formula in cell C7 is:

=FunctionCopyFiles(C3,C5)

Whenever the function calculates, it uses the values in cells C3 and C5 as the source and destination.

If the file copies, the value returned is File Copied; if the file is not copied the value returned is File Not Copied.

Example 8: Copy files based on a list

What if we have a list of files that we want to copy them all at once? We can do that with the following macro.

Sub VBACopyFilesInList()

'Declare variables
Dim copyFromFileRange As Range
Dim copyToFileRange As Range
Dim i As Long
Dim j As Long

'Turn off error checking
On Error Resume Next

'Get ranges
Set copyFromFileRange = Application.InputBox( _
    Title:="Select range", _
    Prompt:="List of files to copy:", _
    Type:=8)
If copyFromFileRange Is Nothing Then Exit Sub

Set copyToFileRange = Application.InputBox( _
    Title:="Select range", _
    Prompt:="List of file destinations:", _
    Type:=8)
If copyToFileRange Is Nothing Then Exit Sub

'Loop through rows
For i = 1 To copyFromFileRange.rows.Count

    'Loop through columns
    For j = 1 To copyToFileRange.columns.Count
    
        FileCopy copyFromFileRange.Cells(i, j), _
            copyToFileRange.Cells(i, j)
    
    Next j
    
Next i

'Turn error checking back on
On Error GoTo 0

End Sub

When we run the macro, input boxes appear for us to select the list of files to copy, and the list of file destinations.

The macro loops through the list and copies each file. If there are no errors all the files will be copied to their new destination.

Example 9: Moving vs copying

There are many occasions when we want to move a file rather than copy it. In these cases, instead of the FileCopy statement, we use the Name statement.

Sub VBAMoveFile()

Name "C:\Users\marks\Documents\Example File.xlsx" As _
    "C:\Users\marks\Documents\Test\Example File.xlsx"

End Sub

The Name statement does not just rename a file, but it also changes the file path. Therefore Name will move a file.

Example 10: Alternative method – FSO

The File System Object (known as FSO) is a reference library that provides additional features for working with files.

While this post is not about the FSO method, it is useful to compare the code.

The CopyFile statement within FSO cannot be called by itself, it requires a reference to the FSO library to be created first.

'Create the variable
Dim FSOLibrary As Object

'Create reference to the library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")

FSO Syntax

The syntax of the FSO CopyFile method is:

FSOLibrary.CopyFile source, destination, [overwrite]

• source – Text string specifying the file to be copied. The argument is required.
• destination – Text string specifying the target file name. This argument is required.
• overwrite – Boolean value specifying if the destination file should be overwritten. True = overwrite, False = do not overwrite. The argument is optional if excluded, the default is false.

FSO Example:

Sub VBACopyFileFSO()

'Declare variables
Dim copyFromFile As String
Dim copyToFile As String
Dim FSOLibrary As Object

'Create the reference to the FSO library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    
'Assign variables to file names
copyFromFile = "C:\Users\marks\Documents\Example File.xlsx"
copyToFile = "C:\Users\marks\Documents\Test\Example File Copied.xlsx"

'CopyFile using FSO method
FSOLibrary.CopyFile copyFromFile, copyToFile, True

'Release the memory
Set FSOLibrary = Nothing

End Sub

Note: FSO can be used in two forms, late binding or early binding. The code above uses late binding, so there is no need to create an explicit reference to the FSO library.

Learn more about the File System Object Method here: https://wellsr.com/vba/2018/excel/introduction-to-the-vba-filesystemobject/

Possible errors

Copying a file during an automation can cause errors. Below are some of the common errors from the VBA FileCopy method.

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.

Also, check the VBA code library for hundreds of reusable code snippets.

Related Posts:

• How to create multiple folders at once with Excel
• VBA code to loop through files in a folder (and sub folders)
• VBA code to copy, move, delete and manage files

---

What next?

Discover how you can automate your work with our Excel courses and tools.