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.