MS Access – VBA – Copy a File

Have you ever needed to make a copy of a file? Well, there are a number of ways that you can do it.

Built-in VBA FileCopy

I find that using the built-in FileCopy function to be the simplest and cleaness method. Below is a simple procedure around it that trap certain common errors.

'---------------------------------------------------------------------------------------
' Procedure : CopyFile
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Copy a file
'             Overwrites existing copy without prompting
'             Cannot copy locked files (currently in use)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSource - Path/Name of the file to be copied
' sDest - Path/Name for copying the file to
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' 1         2007-Apr-01             Initial Release
'---------------------------------------------------------------------------------------
Public Function CopyFile(sSource As String, sDest As String) As Boolean
On Error GoTo CopyFile_Error

    FileCopy sSource, sDest
    CopyFile = True
    Exit Function

CopyFile_Error:
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & sSource & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    End If
    Exit Function
End Function

All you have to do is copy it into a module and then call it as required. Enjoy!

Copying a File Using FSO

As an alternative, here is an example to copy a file using FSO.

'---------------------------------------------------------------------------------------
' Procedure : FSO_FileCopy
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Copy a file
'               Overwrites existing copy without prompting (you can change the varible
'               in the CopyFile method call)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
' Ref       : FSO - https://msdn.microsoft.com/en-us/library/ms127964(v=vs.110).aspx
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSource - Path/Name of the file to be copied
' sDest - Path/Name for copying the file to
'
' Usage:
' ~~~~~~
' FSO_FileCopy("C:\TE.MP\Tab.le1.txt", "C:\TE.MP\Tab.le3.txt")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-06-16              Initial Release - Blog Help
'---------------------------------------------------------------------------------------
Public Function FSO_FileCopy(ByVal sSource As String, _
                             ByVal sDest As String) As Boolean
    On Error GoTo Error_Handler
    Dim oFSO                  As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Call oFSO.CopyFile(sSource, sDest, True)
    FSO_FileCopy = True

Error_Handler_Exit:
    On Error Resume Next
    If Not oFSO Is Nothing Then Set oFSO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FSO_FileCopy" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

23 responses on “MS Access – VBA – Copy a File”

  1. pimsainnum

    I found this error:
    403 – Forbidden: Access is denied.
    You do not have permission to view this directory or page using the credentials that you supplied.

    1. admin Post author

      This looks like a website error and not a VBA error?!
      It is very hard to help you when you do not supply any information on how you were using the code (supply your code in which you have integrated using this function), give me a context (what type of files are you trying to copy, where are they located, etc…)
      One way or another, the message is very clear. Either you made a typo entering your login username/password or you simply do not have the necessary rights to access the folder/files. Try again, and if the problem persists, then contact your Network administrator for assistance.

  5. Alan Sauerbrei

    I found this today. It worked perfectly and saved me a couple hours of troubleshooting.

    Thanks

  6. bob

    just found this link is there a way to automatically not copy or over right a file is already exist, I just want to add new files with out overwriting the ones that are already there?

  8. James Muka

    What would you recommend for folders that have a period in them?
    We have clients that create folders with periods. When I try to use the FileCopy or your CopyFile it errors out. Are there any workarounds for this situation?

    1. Daniel Pineault Post author

      The built-in CopyFile, or my procedure seem to work fine with paths or filenames that include periods?! Can you give me a concrete example of a fully qualified path/filename that I could test with?

      I’ve also updated the example to also now include an FSO example, you might want to try with it instead and see if there is any difference in behavior.

  9. James Muka

    You are correct…I see my problem now.
    Thank you very much for replying so fast.

    Love your blog…it’s great for the Access community!

  10. James Hill

    Is there a way to copy files that have been encrypted in MS Access? I’m using the fso method and it works great unless the .accdb file is encrypted.

    1. Daniel Pineault Post author

      FSO doesn’t care about the files you are trying to copy, encrypted or not. FSO doesn’t know if the accdb it is copying is encrypted, it’s a file, that all it knows. So there is something else going on here.

  11. F ten Brink

    If the destination file allready exists, how can i ad an incrementing number? (even with multiple existing files e.g.(2), (3) etc….

    1. Daniel Pineault Post author

      You could do something like:

      Public Function CopyFile(ByVal sSrcFile As String, ByVal sDestPath As String) As Boolean
' Copies a file and retain original file attributes (created, modified, ...)
    On Error GoTo CopyFile_Error
    '    Dim sSrcFilePath          As String
    Dim sSrcFileName          As String
    Dim sSrcFileExt           As String
    Dim sDestFile             As String
    
    ' Probably a good idea to perform a FielExist check here first before continuing.

    ' Break the Source file down into its componenets
    '    sSrcFilePath = left(sSrcFile, InStrRev(sSrcFile, "\"))
    sSrcFileName = right(sSrcFile, Len(sSrcFile) - InStrRev(sSrcFile, "\"))
    sSrcFileName = left(sSrcFileName, InStrRev(sSrcFileName, ".") - 1)
    sSrcFileExt = right(sSrcFile, Len(sSrcFile) - InStrRev(sSrcFile, "."))

    ' Define the Destination file
    If right(sDestPath, 1) <> "\" Then sDestPath = sDestPath & "\"
    sDestFile = sDestPath & sSrcFileName & "1" & "." & sSrcFileExt

    ' Perform the actual file copy
    FileCopy sSrcFile, sDestFile
    CopyFile = True
    Exit Function

CopyFile_Error:
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & sSrcFile & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    End If
    Exit Function
End Function

      and you’d call it by simply doing

      Call CopyFile("C:\Images\desert.jpg", "C:\Users\Dev\Desktop\")
      1. AS Mughal

        Couldn’t get this right
        but did manage to auto name the destination file using this code;
        Private Sub Command3_Click()
        Dim f As Object
        Dim strfile As String
        Dim varItem As Variant

        Set f = Application.FileDialog(3)
        f.allowMultiSelect = True
        If f.show Then
        For Each varItem In f.selectedItems
        strfile = Dir(varItem)

        strfolder = Left(varItem, Len(varItem) – Len(strfile))
        MsgBox “Folder” & strfolder & vbCrLf & “File: ” & strfile
        Me.Image_Path = strfolder + strfile
        Me.Image_Path1 = strfolder + “Cropped_” + strfile

        Me.Image_Path1.Requery

        DoCmd.Save
        Next
        End If
        Set f = Nothing

        CopyFile Me.Image_Path, Me.Image_Path1
        End Sub
        Then i managed to crop the destination file using your crop image code without over writing the original file.
        Anyways Thanks for the continued support