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
- Arguments and Return Value
- Where we can apply or use VBA FileCopy Function?
Examples of VBA FileCopy Function in Excel
- Copy file from source location to destination location
- Copy opened file from source location to destination location
- Copying a File to a Different Folder
- Copying Multiple Files to a Different Folder
- Copying Multiple Files
- Copying and Renaming a File
- Copying Files Using Variables
- Copying Files Based on User Input
- Copying Files Within the Same Folder
- Copying Files Between Drives
- Overwriting an Existing File
- Copying Files Using Relative Paths
- Copying Files from Network Locations
- Copying an Excel Workbook
- Duplicating a Text File
- Copying Files with Dynamic Paths
- Backing Up an Excel Workbook
- Creating a File Copy with a New Extension
- Copying Files with Error Handling
The syntax of the FileCopy Function in VBA is
The FileCopy function has two arguments in Excel VBA.
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.
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.
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.
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
- Automating file backup processes.
- Archiving important files to a different location.
- Creating duplicates of files for testing purposes.
- Merging files from multiple folders into a single location.
- Generating reports by copying data from various Excel files into a consolidated workbook.
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!
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.