This Access / Excel tutorial explains how to use FSO File related Methods including FileExists, DeleteFile, MoveFile, CopyFile
You may also want to read:
Loop workbooks in folders and subfolders
Access Excel FSO Folder Methods
FSO File Methods
FileSystemObject (FSO) provides an API to access the Windows filesystem such as accessing Drive, TextStram, Folder, File. In this tutorial, I will only explain the use of some important File related Methods. For details, please read the MSDN library.
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
FileExists Method
Check if a file already exists in the directory, returning True or False. See the below example.
DeleteFile Method
DeleteFile Method deletes a File, returns an error if File does not exist.
Syntax
object.DeleteFile filespec[, force]
| object | Required. Always the name of a FileSystemObject. |
| filespec | Required. The name of the file to delete. The filespec can contain wildcard characters in the last path component. |
| force | Optional. Boolean value that is True if files with the read-only attribute set are to be deleted; False (default) if they are not. |
Example
The below Procedures deletes a file if it already exists.
Public Sub delFile()
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists("C:\Users\WYMAN\Desktop\testDel.xlsx") = True Then
FSO.deleteFile ("C:\Users\WYMAN\Desktop\testDel.xlsx")
End If
End Sub
MoveFile Method
MoveFile Method moves a file to another location. Error occurs if source or destination path cannot be found.
Syntax
object.MoveFile source, destination
| object | Required. Always the name of a FileSystemObject. |
| source | Required. The path to the file or files to be moved. The source argument string can contain wildcard characters in the last path component only. |
| destination | Required. The path where the file or files are to be moved. The destination argument can’t contain wildcard characters. |
Example
The below Procedures move a file from Desktop to C:\test\, overwriting existing file in destination.
Public Sub movFile()
movFromPath = "C:\Users\WYMAN\Desktop\testDel.xlsx"
movToPath = "C:\test\testDel.xlsx"
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(movFromPath) = True Then
If FSO.FileExists(movToPath) = False Then
FSO.movefile movFromPath, movToPath
Else
FSO.deleteFile movToPath
FSO.movefile movFromPath, movToPath
End If
End If
End Sub
CopyFile Method
Copy a File to another location. If the File already exists in destination, an error occurs.
Syntax
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 Procedures copy 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
Other File related Methods
Below are some less commonly used Methods related to File, click on the hyperlink to read the details.
| GetFile | Returns a File object corresponding to the file in a specified path. |
| GetFileName | Returns the last component of specified path that is not part of the drive specification. |
