Access Excel VBA copy workbook or file

This Access Excel VBA tutorial explains how to copy workbook or file.

You may also want to read:

FSO File Methods

Worksheets.Copy Method to copy worksheet

Access Excel VBA copy workbook or file

There are two common ways to copy workbook or file

  1. FSO.CopyFile Method
  2. FileCopy Function

I have explained how to use different FSO Methods in my previous post, click to see more details.

Using FSO.CopyFile Method to copy workbook

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

loop_through_workbook

Syntax of FSO.CopyFile

object.CopyFile source, destination[, overwrite]
object Required. Always the name of a FileSystemObject.
source Required. Character string file specification, which can include wildcard characters, for one or more files to be copied.
destination Required. Character string destination where the file or files from source are to be copied. Wildcard characters are not allowed.
overwrite Optional. Boolean value that indicates if existing files are to be overwritten. If True, files are overwritten; if False, they are not. The default is True. Note that CopyFile will fail if destination has the read-only attribute set, regardless of the value of overwrite.

Example

The below Procedure copies a File from Desktop to C:\test\, overwriting existing File.

Public Sub cpyFile()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel.xlsx"
    copyToPath = "C:\test\testDel.xlsx"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(copyFromPath) = True Then
        FSO.copyfile copyFromPath, copyToPath, True
    End If
End Sub

Using FileCopy Function to copy workbook

Note that this is FileCopy Function, while the above example demonstrates CopyFile Method. Click here to see the difference between Function and Method.

Syntax of FileCopy

FileCopy(Source As String, Destination As String)
Source Required. String expression that specifies the name of the file to be copied. Source may include the directory or folder, and drive, of the source file
Destination Required. String expression that specifies the target file name. Destination may include the directory or folder, and drive, of the destination file.

Example

Same as the above example, the below Macro copies a File from Desktop to C:\test\, overwriting existing File.

Note that FileCopy Function does not require FSO object, I declare FSO in order to use the FSO.FileExists Method.

Public Sub cpyFile2()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel.xlsx"
    copyToPath = "C:\test\testDel.xlsx"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(copyFromPath) = True Then
        FileCopy copyFromPath, copyToPath
    End If
End Sub


Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *