Table of Contents
Copying a file from one location to another using VBA (Visual Basic for Applications) involves utilizing the “FileCopy” function. This function allows the user to specify the source file path and the destination file path, and it will create a copy of the file in the specified destination location. This process can be automated by creating a VBA macro that can be run with the click of a button or through a specific trigger. This functionality can be useful for tasks such as backing up files, transferring data between different locations, or creating duplicates for testing purposes. By utilizing VBA, the copy process can be streamlined and easily repeated, saving time and effort for the user.
VBA: Copy File from One Location to Another
You can use the CopyFile method in VBA to copy a file from one folder to another.
Here is one common way to use this method in practice:
Sub CopyMyFile()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
'specify source file and destination folderSourceFile = "C:UsersbobDesktopSome_Data_1soccer_data.txt"
DestFolder = "C:UsersbobDesktopSome_Data_2"'copy fileFSO.CopyFile Source:=SourceFile, Destination:=DestFolder
End Sub
This particular macro copies the file called soccer_data.txt from a folder called Some_Data_1 to a folder called Some_Data_2.
The following example shows how to use this syntax in practice.
Example: How to Copy Files Using VBA
Suppose we have a text file called soccer_data.txt located in a folder called Some_Data_1 on our Desktop:
Now suppose we would like to use VBA to copy this text file to another folder called Some_Data_2 on our Desktop, which currently contains two text files:
Before using VBA to copy this file, we need to first enable Microsoft Scripting Runtime within the VB Editor.
To do so, open the VB Editor, then click Tools, then click References:
In the new window that appears, scroll down until you see Microsoft Scripting Runtime and check the box next to it. Then click OK.
Next, we can create the following macro to copy the file:
Sub CopyMyFile()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
'specify source file and destination folderSourceFile = "C:UsersbobDesktopSome_Data_1soccer_data.txt"
DestFolder = "C:UsersbobDesktopSome_Data_2"'copy fileFSO.CopyFile Source:=SourceFile, Destination:=DestFolder
End Sub
The original soccer_data.txt will still remain in the Some_Data_1 folder as well:
Note: You can find the complete documentation for the CopyFile method .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: