Function To Check If File Or Directory (Folder) Exists

Introduction:
When creating routines, it is commonly helpful to check if a file or directory (folder) exists before attempting to perform some action. Searching the internet will yield several examples of doing either, but this is a routine that I wrote in order to have one routine to test either.

Macro Purpose:

• Check if the supplied path to a file or directory (folder) exists.

Examples of where this function shines:

• When you need to check if a file OR directory exists before you attempt to so something with it (such as load a file).

Macro Weakness(es):

• None identified at this time.

Versions Tested:
This function has been tested extensively with Excel 97 through Excel 2010, and should work with any version of Excel without any modifications.

VBA Code Required:

• Place the following code in a standard module of the workbook you wish to use it in.

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists

    If strFullPath = vbNullString Then Exit Function
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0
End Function

How to use the code:

• Call it from another routine, as shown below.
• Please note that you should provide the full file path to the file/folder as the argument.
• You may supply or ignore the trailing on a directory if you choose. It will work either way.

Public Sub TestFolderExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
    If FileFolderExists("F:Templates") Then
        MsgBox "Folder exists!"
    Else
        MsgBox "Folder does not exist!"
    End If
End Sub

Public Sub TestFileExistence()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Test if directory exists
    If FileFolderExists("F:TestTestWorkbook.xls") Then
        MsgBox "File exists!"
    Else
        MsgBox "File does not exist!"
    End If
End Sub

Acknowledgements:
Thanks to Rory Byrne for pointing out a more efficient way to accomplish the above using the Dir function, as well as Richard Barrett for pointing out that a previous incarnation would throw an error when testing a removable drive.