This Access Excel VBA tutorial explains how to copy workbook or file.
You may also want to read:
Access Excel VBA copy workbook or file
There are two common ways to copy workbook or file
- FSO.CopyFile Method
- 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
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.|
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.|
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