[Excel VBA] How to Copy Files Using FSO (.Copy)

In VBA macros, you often encounter situations where you need to copy files, such as creating backups of data files before processing or duplicating template files to create new reports.

By using the .Copy method of the FileSystemObject (FSO), you can copy files easily and reliably. This article explains the basic method of copying files using FSO and how to control overwriting.

Preparation: Add Reference

To use FSO effectively, it is recommended to open Tools > References in the VBA Editor and check “Microsoft Scripting Runtime”.

Complete VBA Code

The following VBA code copies a file named “DataSource.xlsx” to a subfolder named “Backup”.

' Reference: Microsoft Scripting Runtime
Sub CopyFileWithFSO()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim sourceFile As File
    Dim sourceFilePath As String
    Dim destinationFolderPath As String

    '--- Settings ---
    sourceFilePath = ThisWorkbook.Path & "\DataSource.xlsx"
    destinationFolderPath = ThisWorkbook.Path & "\Backup\"
    '--- End Settings ---

    '--- 1. Check if source file and destination folder exist ---
    If Not fso.FileExists(sourceFilePath) Then
        MsgBox "Source file not found." & vbCrLf & sourceFilePath, vbCritical
        Exit Sub
    End If
    If Not fso.FolderExists(destinationFolderPath) Then
        ' Create folder if it does not exist
        fso.CreateFolder destinationFolderPath
    End If
    
    '--- 2. Get the file object to copy ---
    Set sourceFile = fso.GetFile(sourceFilePath)

    '--- 3. Copy the file using the .Copy method ---
    ' Set the 2nd argument (Overwrite) to True to overwrite if the file exists
    sourceFile.Copy Destination:=destinationFolderPath, Overwrite:=True

    MsgBox "File '" & sourceFile.Name & "' has been copied to" & vbCrLf & _
           "'" & destinationFolderPath & "'."
           
    ' Release objects
    Set sourceFile = Nothing
    Set fso = Nothing

End Sub

Code Explanation

The .Copy Method of the File Object

TargetFileObject.Copy Destination, [Overwrite]

The .Copy method is executed on the File object retrieved using fso.GetFile.

• Destination Argument: Specify the destination path as a string. There are two behaviors depending on how you specify this:
  • If you specify a folder: E.g., "C:\Backup\". The file is copied into that folder with its original name.
  • If you specify a full path: E.g., "C:\Backup\NewData.xlsx". The file is copied and renamed at the same time.
• [Overwrite] Argument (Optional): Specify True or False to define behavior if a file with the same name exists at the destination.
  • True: Overwrites the file without a confirmation message.
  • False (Default): An error occurs, and the macro stops.

Using True is convenient for automation tasks where you always want to update the backup with the latest version.

Application: Creating a Backup with a Timestamp

By applying this technique, you can easily create a macro that generates backups with the execution date and time in the filename for version control.

Sub CreateTimestampedBackup()
    Dim fso As New FileSystemObject
    Dim bookToBackup As File
    Dim backupFilePath As String
    
    ' Target this macro workbook itself for backup
    Set bookToBackup = fso.GetFile(ThisWorkbook.FullName)
    
    ' Create backup filename by adding date and time to the original filename
    backupFilePath = ThisWorkbook.Path & "\" & fso.GetBaseName(bookToBackup) & _
                     "_" & Format(Now, "yyyymmdd_hhnnss") & "." & _
                     fso.GetExtensionName(bookToBackup)

    ' Copy the file to the new path
    bookToBackup.Copy backupFilePath

    MsgBox "Backup created." & vbCrLf & backupFilePath
End Sub

Summary

To copy files using FSO, use the .Copy method of the File object.

1. Get the File object using fso.GetFile("Source Path").
2. Execute .Copy "Destination Path", [Overwrite] on that object.
3. (Recommended) Check for errors using FileExists or FolderExists before execution.

The .Copy method is a fundamental and important function for automating file operations, such as creating backups and duplicating templates.