VBA Copy File to Folder

Excel VBA Macro to Copy File

This page explains about 2 options to do file copy using Vba.

1. CopyFile option from FileSystemObject
2. VBA.Filecopy

The first one has more options like: Adding wild cards with source file & option to overwrite a file if already exists. Vba built in Filecopy function does not have these flexibility.

Copy File using Excel VBA code Snippet

Here is the Vba code that foes file copy.

It uses the Filesystemobject from Microsoft scripting runtime library for this purpose. It has direct option to copy files.

Syntax & Explanation:

    Dim oFso As FileSystemObject
    oFso.copyfile sSourcePath, sDestinationPath, bOverWriteFile
    'SourceFilepath, Destination folder Path (with trailing Slash(\) at end, True or False

    'OR 
    'Use VBA Command
    VBA.FileCopy sSourcePath, sDestinatonPath

Here is the explanation about the Function:

• Sourcepath: Can be a file name or path with wildcard that match list of files.
• DestinationPath: Can only be a folder.
• OverWrite Flag – Boolean – True/False

CopyFile Implementation:

Sub filecopyVBA(sSourcePath As String, sDestinationPath As String)
    'Code from Officetricks
    'Late Binding
    'Dim oFso as object
    'Set oFso  = VBA.CreateObject("Scripting.FileSystemObject")
    
    'Early Binding
    'Add Reference to Microsoft Scripting Runtime
    Dim oFso As FileSystemObject, bOverWriteFile As Boolean
    Dim vFilePathSplit As Variant, sFileName As String
    Dim sPathSep As String
    sPathSep = Application.PathSeparator
    
    If VBA.Right(VBA.Trim(sDestinationPath), 1) <> sPathSep Then
        sDestinationPath = sDestinationPath & sPathSep
    End If
    
    'Before Copying Check if file exists already
    Set oFso = New FileSystemObject
    If oFso.FileExists(sSourcePath) Then
        bOverWriteFile = False
               
        'Check if file already exists
        vFilePathSplit = VBA.Split(sSourcePath, sPathSep, , vbTextCompare)
        sFileName = vFilePathSplit(UBound(vFilePathSplit))
        sFileName = sDestinationPath & sPathSep & sFileName
        
        If oFso.FileExists(sFileName) Then
            If MsgBox("File with same name exists. Overwrite(Y/N)?", vbYesNo) = vbYes Then
                'File Copy - with overwrite option
                bOverWriteFile = True
            Else
                Exit Sub
            End If
        End If
        
        'Filesystemobject CopyFile
        oFso.copyfile sSourcePath, sDestinationPath, bOverWriteFile
    End If
End Sub

In addition to copying file, this code also checks if the file already exists in the folder. If it is already there, then it asks the user response.

External Reference: Microsoft page about the fso copyfile function.