First Published 22 May 2022             Last Updated 22 May 2022


There are several ways to copy or move a file from one folder to another.
You can do it manually by copying the source file and pasting it into the destination folder.

However, copying the file can be automated using the VBA FileCopy function.

Syntax:
      FileCopy(Source As String, Destination As String)

Example usage:
      FileCopy "G:\HomeFiles\Test.xlsx", "D:\Production\Test.xlsx"

The FileCopy function is very simple to use and has several advantages:
a)   It can be used for copying more than one file at the same time.
b)   It takes a similar amount of time to copy several files as it does to copy one file.
c)   It can be used for any file types which you want to copy.

Alternatively, you can use FileSystemObject (FSO) to copy files.

Sub CopyFile()

      Dim fso As Object
      Dim strOldPath As String, strNewPath As String

      Set fso = CreateObject("Scripting.FileSystemObject")

      strOldPath = “ExistingFilePath”
      strNewPath = "NewFilePath"

      'copy file to the new path
      fso.CopyFile strOldPath, strNewPath

      'tidy up
      Set fso = Nothing

End Sub

FileSystemObject provides additional functionality beyond that available with the standard FileCopy function.

Once again, you can copy multiple files at once by looping through each in turn.
Or you can use fso.CopyFolder instead to copy all files from one folder to another

Sub CopyFolder()

'This copies all files and subfolders from FromPath to ToPath.
'If ToPath already exists it will overwrite existing files in this folder
'If ToPath does not exist it will be made for you.

On Error GoTo ErrHandler

      FromPath = "YourSourceFolderPath"
      ToPath = "YourDestinationFolderPath"

      Set fso = CreateObject("scripting.filesystemobject")

      If fso.FolderExists(FromPath) = False Then
            MsgBox "The source path " & vbNewLine & FromPath & vbNewLine & " does not exist", vbCritical, "Error"
            Exit Sub
      End If

      DoEvents

      fso.CopyFolder FromPath, ToPath

ExitHandler:
      On Error Resume Next
      Exit Sub

ErrHandler
      If Err.Number = 53 Or Err.Number = 76 Or Err.Number = 3024 Then
            Resume
      ElseIf Err.Number = 70 Then
            MsgBox "One or more files could not be updated as these were in use", vbCritical, "Update error"
      Else
            MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical
      End If

      Resume ExitHandler

End Sub

Whilst the above methods work well, if you have a lot of large files to copy, both methods can be slow
Additional code is required if you want to monitor progress.

There is a much faster method of copying files which uses a Windows API. A progress dialog is created automatically

Copy this code to a standard module e.g. modCopyAPI

Option Compare Database
Option Explicit

'Type declarations
'============================
Private Type SHFILEOPSTRUCT

#If VBA7 Then

      hWnd As LongPtr
#Else
      hWnd As Long
#End If

wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Long

#If VBA7 Then
      hNameMappings As LongPtr
#Else
      hNameMappings As Long
#End If

lpszProgressTitle As String

End Type
'============================

'API declarations
'============================
#If VBA7 Then       'A2010 or later (32/64-bit)
      Private Declare PtrSafe Function SHFileOperation Lib "shell32.dll" _
            Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
#Else       'A2007 or earlier
      Private Declare Function SHFileOperation Lib "shell32.dll" _
            Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
#End If
'============================

Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10
Private Const FO_COPY = &H2
'============================

Public Function apiFileCopy(src As String, Dest As String, _
      Optional NoConfirm As Boolean = False) As Boolean

      'PARAMETERS: src: Source File (FullPath)
      'dest: Destination File (FullPath or directory)
      'NoConfirm (Optional): If set to true, no confirmation box
      'is displayed when overwriting existing files, and no
      'copy progress dialog box is displayed
      'Returns (True if Successful, false otherwise)

      Dim WinType_SFO As SHFILEOPSTRUCT
      Dim lRet As Long
      Dim lflags As Long

      lflags = FOF_ALLOWUNDO
      If NoConfirm Then lflags = lflags & FOF_NOCONFIRMATION

      With WinType_SFO
            .wFunc = FO_COPY
            .pFrom = src
            .pTo = Dest
            .fFlags = lflags
      End With

      lRet = SHFileOperation(WinType_SFO)
      apiFileCopy = (lRet = 0)

End Function

Example usage:

Sub TestCopy()

      Dim FromPath As String, ToPath As String

      'select a file or folder
      FromPath = "G:\Programs\MendipDataSystems\CommonFiles\UKPAF\Datafiles\"       'source folder to be copied
      ToPath = "G:\MyFiles\ExampleDatabases\APIFileCopy\"       'destination folder

      Call apiFileCopy(FromPath, ToPath, True)       'or set false to prevent the progress dialog being displayed

      'MsgBox "File copy completed"

End Sub

A progress dialog is shown automatically.

You can choose to just show a progress bar . . .

. . . or to show additional detail about the files being copied.

This dialog also allows the file copying to be paused or cancelled at any time

Typically, this method allows several gigabytes of files to be copied in less than a minute.



Download:

The modCopyAPI code is available as a zipped .bas file which can be imported directly into the Visual Basic Editor

Click to download:     modCopyAPI              Approx 2 kB (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 22 May 2022

Return to Code Samples Page

Return to Top