Access Excel FSO Folder Methods

This Access / Excel tutorial explains how to use FSO Folder related Methods including FolderExists, CreateFolder, DeleteFolder, MoveFolder, CopyFolder

You may also want to read:

Loop workbooks in folders and subfolders

FSO File Methods

FSO Folder 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 Folder 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

FolderExists Method

Check if a folder already exists in the directory, returning True or False. See the below example.

CreateFolder Method

CreateFolder Method creates a Folder, it returns an error if Folder already exists.

Syntax

object.CreateFolder(foldername)
object Required. Always the name of a FileSystemObject.
foldername Required. String expression that identifies the folder to create.

Example

The below Procedures create a folder if not exists.

Public Sub mkFolder()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists("C:\Users\WYMAN\Desktop\testDel") = False Then
        FSO.createFolder ("C:\Users\WYMAN\Desktop\testDel")
    End If
End Sub

DeleteFolder Method

DeleteFolder Method deletes a Folder regardless whether it has contents inside. The method returns an error if Folder does not exist.

Syntax

object.DeleteFolder folderspec[, force]
object Required. Always the name of a FileSystemObject.
folderspec Required. The name of the folder to delete. The folderspec can contain wildcard characters in the last path component.
force Optional. Boolean value that is True if folders with the read-only attribute set are to be deleted; False (default) if they are not.

Example

The below Procedures deletes a folder if it already exists.

Public Sub delFolder()
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists("C:\Users\WYMAN\Desktop\testDel") = True Then
        FSO.deleteFolder ("C:\Users\WYMAN\Desktop\testDel")
    End If
End Sub

MoveFolder Method

MoveFolder Method moves a folder to another location. Error occurs if source or destination cannot be found.

Syntax

object.MoveFolder ( source, destination );

object Required. Always the name of a FileSystemObject.
source Required. The path to the folder or folders to be moved. The source argument string can contain wildcard characters in the last path component only.
destination Required. The path where the folder or folders are to be moved. The destination argument can’t contain wildcard characters.

Example

The below Procedures move a folder from Desktop to C:\, overwriting existing folder in destination.

Public Sub movFolder()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel"
    copyToPath = "C:\testDel"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(copyFromPath) = True Then
        If FSO.FolderExists(copyToPath) = False Then
           FSO.movefolder copyFromPath, copyToPath
        Else
           FSO.deleteFolder copyToPath
           FSO.movefolder copyFromPath, copyToPath
        End If
    End If
End Sub

CopyFolder Method

Copy a Folder to another location. If the Folder already exists in destination, an error occurs.

Syntax

object.CopyFolder ( source, destination[, overwrite] )
object Required. Always the name of a FileSystemObject.
source Required. Character string folder specification, which can include wildcard characters, for one or more folders to be copied.
destination Required. Character string destination where the folder and subfolders from source are to be copied. Wildcard characters are not allowed.
overwrite Optional. Boolean value that indicates if existing folders are to be overwritten. If true, files are overwritten; if false, they are not. The default is true.

Example

The below Procedures copy a folder from Desktop to C:\, overwriting existing Folder.

I tested the overwrite argument but it fails to overwrite an existing folder, so I delete the folder instead.

Public Sub cpyFolder()
    copyFromPath = "C:\Users\WYMAN\Desktop\testDel"
    copyToPath = "C:\testDel"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FolderExists(copyFromPath) = True Then
        If FSO.FolderExists(copyToPath) = False Then
           FSO.copyfolder copyFromPath, copyToPath
        Else
           FSO.deleteFolder copyToPath
           FSO.copyfolder copyFromPath, copyToPath
        End If
    End If
End Sub

Other Folder related Methods

Below are some less commonly used Methods related to Folder, click on the hyperlink to read the details.

GetFolder Returns a Folder object corresponding to the folder in a specified path.
GetParentFolderName Returns a string containing the name of the parent folder of the last component in a specified path.
GetSpecialFolder Returns the special folder object specified.

 

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 *