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
