Access Excel FSO File Methods

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

loop_through_workbook

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.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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 *