Introduction
The File System Object (FSO) provides access to a computer’s file system. The particular object contains 3 object collections, 4 other objects, as well as several properties and methods (see the picture below). FSO is a quite powerful object, but it should be used with caution; since it can manipulate files that might belong to the operating system, a poorly written VBA code can even cause malfunctions in the entire computer. I would suggest you take a backup of your folder/file that you need to manipulate (copy, move, delete, etc.) before running any code. In any case, be careful when you working with FSO, and, especially, when you delete files.
The section below focuses on three useful properties of the File System Object – Drive Exists, Folder Exists and File Exists – which can be used to determine the existence of a drive, folder, and file respectively. I have adapted these properties into three corresponding VBA functions, which return string messages depending on whether or not the specified drive/folder/file exists.
VBA code
DriveExists function
All functions share the same approach; create the FSO object and then apply the corresponding property. The VBA code of the DriveExists function is presented first.
Option Explicit
Function DriveExists(DriveName As String) As String
'-----------------------------------------------------------------------------
'Determines if the specified drive exists. It returns a string message.
'The function uses the DriveExists method of File System Object to determine
'if the drive exists. The DriveName must be in the form: "F:\", or "F".
'Written By: Christos Samaras
'Date: 19/01/2014
'Last Updated: 01/04/2020
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'-----------------------------------------------------------------------------
'Declare the necessary variable.
Dim FSO As Object
'Without the next statement, the Find function might fail.
On Error Resume Next
'Check if the drive's name is not empty.
If DriveName = vbNullString Then
DriveExists = "Drive name is empty."
Exit Function
End If
'Check if there are more than 3 characters in the drive's name.
If Len(DriveName) > 3 Then
DriveExists = "Long drive name."
Exit Function
End If
'Create the File System Object.
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check if the FSO was created.
If FSO Is Nothing Then
DriveExists = "Couldn't create the FSO!"
Exit Function
End If
'Check if there is a colon in the driver's name. If not, add it.
If WorksheetFunction.Find(":", DriveName) <> 2 Then
DriveName = DriveName & ":"
End If
'Check if there is a backslash in the driver's name. If not, add it.
If WorksheetFunction.Find("\", DriveName) <> 3 Then
DriveName = DriveName & "\"
End If
'Use the DriveExists method of FSO to determine if the drive exists.
If FSO.DriveExists(DriveName) = True Then
DriveExists = "Drive exists."
Else
DriveExists = "Drive does NOT exist."
End If
'Release the object.
Set FSO = Nothing
'Return to the normal behavior (just in case the function is called in other subs).
On Error GoTo 0
End Function FolderExists function
The FolderExists function follows:
Option Explicit
Function FolderExists(FolderPath As String) As String
'-----------------------------------------------------------------------------
'Determines if the specified folder exists. It returns a string message.
'The function uses the FolderExists method of File System Object to determine
'if the folder exists. A valid FolderPath can be for example: "C:\Windows".
'Written By: Christos Samaras
'Date: 19/01/2014
'Last Updated: 01/04/2020
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'-----------------------------------------------------------------------------
'Declare the necessary variable.
Dim FSO As Object
'Check if the folder path is not empty.
If FolderPath = vbNullString Then
FolderExists = "Folder path is empty."
Exit Function
End If
'Create the File System Object.
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check if the FSO was created.
If FSO Is Nothing Then
FolderExists = "Couldn't create the FSO!"
Exit Function
End If
'Use the FolderExists method of FSO to determine if the folder exists.
If FSO.FolderExists(FolderPath) = True Then
FolderExists = "Folder exists."
Else
FolderExists = "Folder does NOT exist."
End If
'Release the object.
Set FSO = Nothing
End Function FileExists function
Finally, the FileExists function is given below.
Option Explicit
Function FileExists(FilePath As String) As String
'----------------------------------------------------------------------------
'Determines if the specified file exists. It returns a string message.
'The function uses the FileExists method of File System Object to determine
'if the file exists. A valid FilePath can be for example: "C:\Test.pdf".
'Written By: Christos Samaras
'Date: 19/01/2014
'Last Updated: 01/04/2020
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'----------------------------------------------------------------------------
'Declare the necessary variable.
Dim FSO As Object
'Check if the file path is not empty.
If FilePath = vbNullString Then
FileExists = "File path is empty."
Exit Function
End If
'Create the File System Object.
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check if the FSO was created.
If FSO Is Nothing Then
FileExists = "Couldn't create the FSO!"
Exit Function
End If
'Use the FileExists method of FSO to determine if the file exists.
If FSO.FileExists(FilePath) = True Then
FileExists = "File exists."
Else
FileExists = "File does NOT exist."
End If
'Release the object.
Set FSO = Nothing
End Function PathExists function (not-using FSO)
The above functions were demonstrated to give you some insights into FSO usage. It should be highlighted, that instead of these functions, a much simpler function can be developed, which will use the Dir function. The Dir function returns a string representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
The PathExists function that follows implements the Dir function to determine the drive, folder, and file existence. Keep in mind that the PathExists function will NOT work correctly if your input is a drive letter (e.g. “G:”) that belongs to an EMPTY USB drive. In cases like this, use the DriveExists function (does not have this limitation).
Option Explicit
Function PathExists(Path As String) As String
'--------------------------------------------------------------
'Determines if a folder/file path is valid, without using FSO.
'This function uses the Dir function.
'Written By: Christos Samaras
'Date: 19/01/2014
'Last Updated: 01/04/2020
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'--------------------------------------------------------------
On Error Resume Next
'Check if the path is not empty.
If Not Path = vbNullString Then
'Check if the Dir function returns an empty string. If no, the path is valid.
If Not Dir(Path, vbDirectory) = vbNullString Then
PathExists = "Path exists."
Else
'Empty string, the path is invalid.
PathExists = "Path does NOT exist."
End If
Else
'The path was not provided (blank/empty path).
PathExists = "Path does NOT exist."
End If
On Error GoTo 0
End Function Two different ways – one specific and one generic – were presented for determining if a drive/folder/file exists. You can choose the function that best fits your needs.
Downloads
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
FSO related applications
The list below contains various applications that I have developed and were based on the objects, properties and methods of the File System Object. You can download them for free:
- Folders, Subfolders & Files
- Move Files To Subfolders
- Read MP3 Tags
- List All Your VBA Procedures
- Organize Your Office Files
- Quick Back Up Of Your Files
Page last updated: 01/04/2020
