Excelguru Help Site - 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.

Code:

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists
    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.

Code:

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:\Test\TestWorkbook.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.

Knowledge Base Articles By Category

Function To Check If File Or Directory (Folder) Exists

Rate this article

Donations

Recent Forum Posts

How to Rename a File from English to Other Language?

Link a series name to a cell using Excel 2010 VBA

How to Rename a File from English to Other Language?

Shared file\macro & IP address

Shared file\macro & IP address