VBA FILECOPY

What is Excel FileCopy Function?

The VBA (Visual Basic for Applications) FileCopy function is a built-in function used to copy a file from one location to another. It takes two arguments: the source file path and the destination file path. The function copies the file from the source path to the destination path. If the destination file already exists, it will be overwritten without warning.

Consider the following example:

In this sub-procedure, call the VBA FileCopy function and set the source file and destination file. Here, the file to be copied is in D drive.

We can see that the file has been copied from one place to another based on the file name given earlier. Here, the VBA FileCopy and Rename has been implemented.

Note: If you run the same code multiple times, there won’t be duplicates of the file created in the destination directory, instead VBA FileCopy overwrite is triggered as explained earlier.

What is Excel FileCopy Function?

FileDialog is a built-in VBA function used to copy files from one location to another.
The syntax is as follows: FileCopy SourceFile, DestinationFile.
Confirm the destination directory exists and create it if needed.
FileCopy overwrites the destination file if it exists.
File attributes and permissions are also not copied. If you need them, replicate file attributes and permissions if necessary.

Syntax

The syntax for the VBA FileCopy folder is:

FileCopy SourceFile, DestinationFile

Where,

SourceFile: This is the path and name of the file you want to copy.

DestinationFile: This is the path and name of the copy you want to create.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How to Use Excel VBA FileCopy Function?

To create an example where the Date Function is implemented, follow the steps below.

Open an Excel WorkBook and click the “Developer” tab. In that, select “Visual Basic” on the far left of the Excel toolbar.

It opens the VBA Editor. In the Editor’s toolbar, click “Insert” and then select “Module” in the drop-down section. Now, let’s start coding in Excel VBA.
Start with naming the subroutine to copy a file from one location to another.
Select the file you want to copy by right-clicking on the file and selecting “Copy as Path” for your purposes.
Paste the path you copied in the VBA FileCopy function.
Go back to the files and select your destination path.

Here, the destination path is “D:\ExcelMojo”.
Paste the destination path along with the filename you want to save the copied file in.

Note: If you simply paste the directory name, for the destination, you will get a file not found error.

Code:

Sub CopyFileExample2()
FileCopy “D:\Excel VBA\example.txt”, “D:\ExcelMojo\copy_example.txt”
End Sub
Click “F5” or the “Run” icon on the activity bar in the VBA Editor. This will paste a copy of the file “example.txt” into the destination folder.

From this, we know how to use VBA FileCopy for our purposes. Let us see some interesting examples below which use VBA FileCopy.

Examples

See the different ways in which VBA FileCopy and rename is used in these examples.

Example #1

In this example, you want to save a copy of an Excel CSV file in the same folder as another name so that any changes you make will reflect on the original file, and the copied file will remain the same. It is helpful if you want to save backups.

Step 1: Define a subroutine to copy the Excel CSV file using VBA FileCopy folder.

Step 2: Copy the file path of the Excel file to be copied.

Step 3: Paste the copied directory with the VBA FileCopy function.

Step 4: Copy the destination folder path of your choice.

Step 5: Paste the destination directory with the file name included.

VBA FileCopy in Excel - Example 1 - Step 5

Separate the source file directory and destination file directory with a comma.

Step 6: Write a Message Box function to indicate that the file is backed up successfully.

Code:

Sub CopyExcelFilesForBackup()
FileCopy “D:\Excel VBA\autogen_data.csv”, “D:\ExcelMojo\autogen_data_copy.csv”
MsgBox “File backed up successfully!”
End Sub

Step 7: Run the subroutine. It will display a Message Box indicating that the file copying has been done.

VBA FileCopy in Excel - Example 1 - Step 7a

VBA FileCopy in Excel - Example 1 - Step 7b

The Excel CSV file has been copied and saved successfully.

Example #2

Using Variables to Store Source Path and Destination Path

Now you know how to copy files from one folder to another. VBA FileCopy can also copy multiple files from one folder to another. Let’s see how to do that.

Step 1: Initialize a sub-routine to copy multiple files.

Step 2: Define string variables to store the source and destination path. Define a fileNames array and an iterative variable to run through each array element/file.

Step 3: Define the source and the destination directory.

Step 4: Define the filenames to be copied into an array, as shown.

Step 5: Initialize a FOR-loop to iterate through the array and combine it with the source and destination folders as shown.

Step 6: Write a Message Box function indicating whether the files have been copied successfully or not.

Code:

Sub CopyFiles()
Dim sourcePath As String
Dim destPath As String
Dim fileNames As Variant
Dim fileName As Variant
sourcePath = “D:\Excel VBA\”
destPath = “D:\ExcelMojo\”
fileNames = Array(“phishtank.csv”, “Iris.csv”, “german_credit_data.csv”)
For Each fileName In fileNames
FileCopy sourcePath & fileName, destPath & fileName
Next fileName
MsgBox “Files copied successfully!”
End Sub

Step 7: Click the green arrow button when you want to run the code. It will pop up a Message Box once Run.

Excel VBA FileCopy - Example 2 - Step 7a

If we look at the destination folder, all the files have been copied successfully.

Excel VBA FileCopy - Example 2 - Step 7b

Error with FileCopy

There are many errors encountered when using VBA FileCopy. Some of the most common ones are:

File Not Found (Error 53): If the source file specified in FileCopy does not exist, you will encounter a “File Not Found” error (Error 53). You should check if the file exists before attempting to copy it.

For Example:

The file to be copied isn’t there in the folder. Hence it will throw an error.

Path/File Access Error (Error 75): This error occurs if you don’t have the necessary permissions to read the source file or write to the destination file or directory. Ensure that you have the required permissions.

If you try to copy a file into a restricted folder, as seen below, you will get a VBA FileCopy Permission denied error.

Path Not Found (Error 76): If the source or destination paths provided in FileCopy are invalid or do not exist, you will encounter a “Path Not Found” error (Error 76). Double-check the paths for accuracy.

For example, if you do not define the file name, you will get this error.

We know that “C:/” is not a valid path. Hence we get the error:

File Already Exists (No Error): If the destination file already exists, FileCopy will overwrite it without any error message. This behavior can lead to data loss if not handled carefully.

File in Use (No Error): If the source file is open by another application or user, you may encounter issues. FileCopy does not check for file locks or open file handles and may result in incomplete or corrupted copies.

Long File Paths: Long file paths or names can sometimes cause issues when copying files, especially when dealing with network paths. Ensure that you are using valid and accessible paths.

FileCopy Interrupted (No Error): If there’s any interruption during the copy process (e.g., system crash, abrupt program termination), the FileCopy may be incomplete or result in a corrupted file.

Important Things to Note

Verify the source file’s existence before copying.
Be cautious of file locks; if the source file is open because of another application or user, it may lead to issues during the copy process.
Ensure that the source and destination file paths, including filenames, are within the limitations of the operating system.
If there is any interruption during the copy process (e.g., system crash or abrupt program termination), the FileCopy may be incomplete or result in a corrupted file.
Be aware that FileCopy does not copy file attributes or permissions. If these attributes are critical, additional steps may be required to replicate them on the copied file.
Use proper file paths, including valid and accessible network paths if applicable.
Thoroughly test your code with various scenarios, including different file types, sizes, and locations, to ensure that it behaves as expected.

Frequently Asked Questions (FAQs)

1. Why is Excel VBA FileCopy Function not working?

• Incorrect file paths.
• Insufficient permissions.
• Overwriting issues.
• Existing file locks.
• Source file not found.
• Destination path issues.
• Error handling missing.
• File in use by another process.
• FileCopy interrupted.

2. What are the advantages of using the FileSystemObject over FileCopy in VBA?

• Versatility: The FileSystemObject is more versatile for file and folder management tasks.
• Advanced Operations: It supports advanced file and folder operations beyond copying.
• Better Error Handling: Provides more robust error handling capabilities.
• Attributes Control: Allows for managing file and folder attributes.
• File Properties: Provides access to file properties like size and dates.

3. Can I copy multiple files at once using VBA?

Yes, you can copy multiple files at once using VBA with the help of a for-loop. Define the directory names for the source path and destination path and define the files in an array as shown.

sourcePath = "C:\" destPath = "C:\Backup" fileNames = Array("A.csv", "B.csv", "C.csv")

For Each fileName In fileNames FileCopy sourcePath & fileName, destPath & fileName Next fileName

4. How can I overwrite an existing file during the copy process in VBA?

• Set a reference to “Microsoft Scripting Runtime” in VBA tools.
• Use the FileSystemObject to copy and overwrite a file.
• Check if the destination file exists and delete it if it does.
• Then, copy the file from the source to the destination using fso.CopyFile.

VBA FILECOPY