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 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
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. |
