In this article I will explain how you can use VBA to copy a file.

Basics:

The function below receives as input 2 file paths. The first parameter determines the location of the file to copy. The second  determines the location to copy the file to:

'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'copy file
Call objFSO.CopyFile(strPathSource, strPathOutput)
End Function


The line below does the copying. The first parameter is the full path of the file to copy. The second parameter is full path to copy the file to (Including the output file name):

Call objFSO.CopyFile(strPathSource, strPathOutput)


The code below tests the functions:

Sub Example1()
Call Copy_File("D:StuffBusinessTempFiletemp.xlsx", _
    "D:StuffBusinessTemp2OutputFile.xlsx")
End Sub


It is assumed that there is a file with the name “Filetemp.xlsx” in the directory “D:StuffBusinessTemp”. The copied file will be named “OutputFile.xlsx” in the directory “D:StuffBusinessTemp2OutputFile”.

See also:

• Microsoft MSDN CopyFile Method

Overwriting:

As explained in the previous section the line below will copy the file located at the path specified by the first input parameter to the location specified by the second input parameter:

Call objFSO.CopyFile(strPathSource, strPathOutput)


The function accepts a third optional parameter of the type boolean. If omitted or passed True it will overwrite the files at the destination path. If passed False it will not overwrite:

'Copy and overwrite
Call objFSO.CopyFile(strPathSource, strPathOutput, True)


The full code can be seen below:

Sub Example2()
Call Copy_File2("D:StuffBusinessTempFiletemp.xlsx", _
    "D:StuffBusinessTemp2OutputFile.xlsx")
End Sub

'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File2(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Copy and overwrite 
Call objFSO.CopyFile(strPathSource, strPathOutput, True)
End Function


Note: The default value for the third parameter is True. Therefore if don’t pass anything as the last parameter the program will try to overwrite the file.

Run-time Error ’70’, Permission Denied:

This error will occur if you try to overwrite a file that is open by the user or another application. This can overcome by using an error handler:

Sub Example3()
Call Copy_File3("D:StuffBusinessTempFiletemp.xlsx", _
    "D:StuffBusinessTemp2OutputFile.xlsx")
End Sub
'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File3(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo lblError:
'will overwrite
Call objFSO.CopyFile(strPathSource, strPathOutput, True)
Set objFSO = Nothing
Exit Sub

lblError:
Err.Clear
Set objFSO = Nothing
End Sub


Run-time Error ’58’, File Already Exists:

This error occurs when you have the overwrite property set to False and you attempt to copy the file to a location that another file with the same name exists. Similar to the previous error, the error can be overcome by using an error handler:

Sub Example4()
Call Copy_File4("D:StuffBusinessTempFiletemp.xlsx", _
    "D:StuffBusinessTemp2OutputFile.xlsx")
End Sub

'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File4(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo lblError:
'will overwrite
Call objFSO.CopyFile(strPathSource, strPathOutput, False)
Set objFSO = Nothing
Exit Sub

lblError:
Err.Clear
Set objFSO = Nothing
End Sub


Run-time Error ’53’, File Not Found:

This error will occur when the path you have specified for the source file does not exist. A good way for overcoming this error would be to check if the file exists before attempting to copy it. I have explained this topic in the article below:

• VBA Check File Exists

A code that would make this check before copying the file can be seen below:

Sub Example5()
Call Copy_File5("D:StuffBusinessTempFiletemp2.xlsx", _
    "D:StuffBusinessTemp2OutputFile.xlsx")
End Sub

'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File5(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strPathSource) Then
    'will overwrite
    Call objFSO.CopyFile(strPathSource, strPathOutput, False)
End If
Set objFSO = Nothing
End Sub


You can download the file and code related to this article from the link below:

• Copy File.xlsm

See also:

• VBA Delete File
• Find and List all Files and Folders in a Directory
• Excel VBA, Find and List All Files in a Directory and its Subdirectories

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com