VBA FileCopy Function in Excel

VBA FileCopy function in Excel is categorized as a File and Directory function. This built-in VBA FileCopy function copies a file from the source directory to the destination directory. If you use FileCopy function on a currently open file, returns an error.

Visual Basic for Applications (VBA) is a powerful programming language that allows users to automate tasks and enhance functionality in Microsoft Excel. One commonly used feature is the FileCopy function. Among the many functions available in VBA, the FileCopy function stands out as a crucial tool for handling file operations.

This function use in either procedure or function in a VBA editor window in Excel. We can use this VBA FileCopy Function in any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the FileCopy function, where we can use this FileCopy Function and real-time examples to demonstrate its versatility in Excel VBA.

Syntax of VBA FileCopy Function

The syntax of the FileCopy Function in VBA is

FileCopy(Source,Destination)

Arguments and Return Value

The FileCopy function has two arguments in Excel VBA.
where
Source: It is a mandatory string parameter. The source argument represents the source file path that you want to copy. It may include folder or directory or drive.
Destination: It is a mandatory string parameter. The destination argument represents the destination file path that you want to copy the file to. It may include folder or directory or drive.

Return Value: The VBA FileCopy function doesn’t have a return value. It simply copies the file from the source location to the destination location.

Where we can apply or use VBA FileCopy Function?

We can use this FileCopy Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

1. Copy file from source location to destination location

Here is a simple example of the VBA FileCopy function. This below example copies file from specified source location to specified destination location. Now same file file is available in two different locations.

'Copy file from source location to destination location
Sub VBA_FileCopy_Function_Ex1()
    
    Dim sSourceFile As String
    Dim sDestinationFile As String
    
    sSourceFile = "C:\VBAF1\VBA Functions\VBA Text Functions\VBA Functionsa.xlsm"
    sDestinationFile = "C:\VBAF1\VBA Functions\VBA Functionsa.xlsm"
    
    FileCopy sSourceFile, sDestinationFile

    MsgBox "Successfully file Copied.", vbInformation, "VBA FileCopy Function"

End Sub

Output: Here is the screen shot of the first example output.

2. Copy opened file from source location to destination location

Here is a simple example of the VBA FileCopy function. This below example tries to copy a file from source location to destination location. But it returns an error. Because the file is opened.

'Copy opened file from source location to destination location
Sub VBA_FileCopy_Function_Ex2()
    
    Dim sSourceFile As String
    Dim sDestinationFile As String
    
    sSourceFile = "C:\VBAF1\VBA Functions\VBA Text Functions\VBA Function Example File.xlsm"
    sDestinationFile = "C:\VBAF1\VBA Functions\VBA Function Example File.xlsm"
    
    FileCopy sSourceFile, sDestinationFile
   
End Sub

Output:Here is the screen shot of the second example output.

3. Copying a File to a Different Folder

This below example copies a file named “File1.txt” from Folder1 to Folder2.

Sub VBAF1_Copying_a_File_to_a_Different_Folder()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder2\File.txt"

End Sub

4. Copying Multiple Files to a Different Folder

This example demonstrates how to copy multiple files from Folder1 to Folder2.

Sub VBAF1_Copying_Multiple_Files_to_a_Different_Folder()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder2\File1.txt"
    FileCopy "C:\VBAF1\Folder1\File2.txt", "C:\VBAF1\Folder2\File2.txt"

End Sub

5. Copying Multiple Files

This example uses a loop to copy multiple files from one folder to another, maintaining the original filenames.

Sub VBAF1_Copying_Multiple_Files()

    Dim filesToCopy As Variant
    Dim destinationPath As String
    
    filesToCopy = Array("C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder1\File2.txt", "C:\VBAF1\Folder1\File3.txt")
    destinationPath = "C:\VBAF1\Folder2\"
    
    For Each File In filesToCopy
        FileCopy File, destinationPath & Mid(File, InStrRev(File, "\") + 1)
    Next File
End Sub

6. Copying and Renaming a File

Let us see the below example, the file “file.txt” is copied to Folder2 and renamed as “newfile.txt”.

Sub VBAF1_Copying_and_Renaming_a_File()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder2\File4.txt"
    
End Sub

7. Copying Files Using Variables

This example demonstrates how to use variables to store the source and destination paths for copying files.

Sub VBAF1_Copying_Files_Using_Variables()
    
    'Variable Declaration
    Dim sourceFile As String
    Dim destinationFile As String
    
    'Assign folder path to a variable
    sourceFile = "C:\VBAF1\Folder1\File1.txt"
    destinationFile = "C:\VBAF1\Folder2\File1.txt"
    
    'Copying File1 from Folder1 to Folder2
    FileCopy sourceFile, destinationFile
    
End Sub

8. Copying Files Based on User Input

This example prompts the user to enter the source and destination file paths, allowing for dynamic file copying.

Sub VBAF1_Copying_Files_Based_on_User_Input()
    
    'Variable Declaration
    Dim sourceFile As String
    Dim destinationFile As String
    
    'Assign user input data to a variable
    sourceFile = InputBox("Enter the source file path:", "VBAF1")
    destinationFile = InputBox("Enter the destination file path:", "VBAF1")
    
    'Copying File1 from Folder1 to Folder2
    FileCopy sourceFile, destinationFile
    
End Sub

Input Screenshots:

9. Copying Files Within the Same Folder

The below example showcases how to copy a file within the same folder and changing its name.

Sub VBAF1_Copying_Files_Within_the_Same_Folder()

    FileCopy "C:\VBAF1\Folder1\File4.txt", "C:\VBAF1\Folder1\newFile4.txt"

End Sub

10. Copying Files Between Drives

This example illustrates how to copy a file from one drive (C:) to another drive (E:).

Sub VBAF1_Copying_Files_Between_Drives()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "E:\VBAF1\Folder1\File1.txt"

End Sub

11. Overwriting an Existing File

The below example demonstrates how the existing file is overwritten in the destination folder, if a file with the same name already exists in the destination folder.

Sub VBAF1_Overwriting_an_Existing_File()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder2\File1.txt"

End Sub

12. Copying Files Using Relative Paths

This example explains how to copy files using relative paths, where the source and destination folders are located within the same directory as the Excel file.

Sub VBAF1_Copying_Files_Using_Relative_Paths()

    FileCopy ".\Folder1\File1.txt", ".\Folder2\File1.txt"
    
End Sub

13. Copying Files from Network or Server Locations

This example demonstrates how to copy a file from a network or server location to a local folder.

Sub VBAF1_Copying_Files_from_Network_Locations()

    FileCopy "\\Server\Shared\Folder1\File1.txt", "C:\VBAF1\Folder2\File1.txt"
    
End Sub

14. Copying an Excel Workbook

The following example copies the file “Workbook1.xlsx” from “Folder1” to “Folder2” using the FileCopy function.

Sub VBAF1_Copying_an_Excel_Workbook()

    FileCopy "C:\VBAF1\Folder1\Workbook1.xlsx", "C:\VBAF1\Folder2\Workbook1.xlsx"
    
End Sub

15. Duplicating a Text File

In this case, the FileCopy function duplicates “File1.txt” and creates a new file named “File2.txt” with the same content.

Sub VBAF1_Duplicating_a_Text_File()

    FileCopy "C:\VBAF1\Folder1\File1.txt", "C:\VBAF1\Folder1\File2.txt"
    
End Sub

16. Copying Files with Dynamic Paths

Here, the source and destination paths are stored in variables, allowing you to dynamically specify different paths for each file copy operation.

Sub VBAF1_Copying_Files_with_Dynamic_Paths()
    
    'Variable declaration
    Dim sourcePath As String
    Dim destinationPath As String
    
    'Assign path to variables
    sourcePath = "C:\Source\Folder1\"
    destinationPath = "C:\Destination\Folder2\"
    
    FileCopy sourcePath & "File1.txt", destinationPath & "File1.txt"
    
End Sub

17. Backing Up an Excel Workbook

The below example copies the current workbook to a backup folder with a unique filename name with current date and time to ensure workbookm version control.

Sub VBAF1_Backing_Up_an_Excel_Workbook()
    
    'Variable declaration
    Dim backupPath As String
    
    'Backup folder path
    backupPath = "C:\Backup\"
    
    'Backup file with
    FileCopy ThisWorkbook.FullName, backupPath & "Backup_" & Format(Now, "ddmmyyyy_hhmmss") & ".xlsx"
    
End Sub

18. Creating a File Copy with a New Extension

In this below example, the FileCopy function creates a copy of “File.docx” with a new extension of “File.pdf”.

Sub VBAF1_Creating_a_File_Copy_with_a_New_Extension()

    FileCopy "C:\VBAF1\Folder1\File.docx", "C:\VBAF1\Folder2\File.pdf"

End Sub

19. Copying Files with Error Handling

In this example, error handling is implemented to handle scenarios where the source file does not exist.

Sub VBAF1_Copying_Files_with_Error_Handling()
    'Ignores eroor
    On Error Resume Next
    FileCopy "C:\Nonexistent\File1.txt", "C:\VBAF1\Folder2\File1.txt"
   
    If Err.Number <> 0 Then
        MsgBox "File copy failed: " & Err.Description
    End If
    
    On Error GoTo 0
    
End Sub

Use Cases of the VBA FileCopy Function:

1. Automating file backup processes.
2. Archiving important files to a different location.
3. Creating duplicates of files for testing purposes.
4. Merging files from multiple folders into a single location.
5. Generating reports by copying data from various Excel files into a consolidated workbook.

Conclusion:

The FileCopy function in VBA provides a convenient and straightforward way to copy files from one location to another. Its flexibility allows for a wide range of use cases, from simple file copying to more complex scenarios involving dynamic paths and user input. Whether you need to duplicate workbooks, create backups, or manage file transfers, this function offers a versatile solution. By mastering this function, you can streamline and explore real-time examples, your Excel workflows and streamline file operations and enhance your productivity. Experiment with the above mentioned multiple unique examples provided in this blog post to explore the full potential of the FileCopy function. Happy coding!

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Pivot Table Tutorials