• Function To Check If File Or Directory (Folder) Exists

    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
        On Error GoTo EarlyExit
        If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
        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!"
            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!"
            MsgBox "File does not exist!"
        End If
    End Sub
    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.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 5 Comments
    1. wonderer2828's Avatar
      wonderer2828 -
      In Excel 2003, I called this function from within a loop that depends on the Dir function to obtain the next file in a folder. It appears that the embedded Dir call in this function interferes with the loop control use of the Dir function. Consider the following meta-code:

      Sub ProcessWorkbooksInFolder(InputFolder As String, FileFilter As String)
      Dim fn As String
      Dim oLOG As String
      oLOG = "C:\ErrorLog.txt"

      fn = Dir(InputFolder & FileFilter) ' the first file name in the folder
      Do While fn <> ""
      Workbooks.Open InputFolder & fn

      ' ... Do Something ...

      If FileFolderExists(oLOG) Then
      Kill oLOG
      End If

      fn = Dir ' the next file name in the folder
      End Sub
    1. strive4peace's Avatar
      strive4peace -
      thanks, Ken ~ revamping old code that I apparently didn't test too well (works fine as long as the DRIVE exists). Now I will isolate it; you got my mind on another track ~
    1. Ken Puls's Avatar
      Ken Puls -
      Good stuff Crystal, hope it works better for you.

      Wonderer2828, I realize this is a late reply (for some reason I don't get notified of comments on the articles), but I'd tend to use FSO to loop through files rather than DIR. I think you'd find that more reliable and faster, even though it actually takes more code.
    1. dainjah's Avatar
      dainjah -
      terrible terrible method ...... word of warning, Never Ever use "GOTO"

    1. sgj's Avatar
      sgj -
      agree with GOTO comment in principle, but even when excel VBA is structured nicely the only way to switch an 'On Error . . . ' invoke command off, is by 'On Error GoTo 0'.

      ... changed to...
      Public Function FileFolderExists(strFullPath As String) As Boolean
      On Error resume next
      If Not Dir(strFullPath, vbDirectory) = vbNullString Then
      FileFolderExists = True
      End If
      On Error GoTo 0
      End Function

      Have sent your comment to another, just to show I am not alone.... strength in numbers : )
  • MVP Logo
  • Recent Forum Posts


    Looking for exact match and return value

    What you've attached and what you describe do not match.
    Column H is the same in both file 1 and 2.

    What's with the Dashboard.xlsx...

    NoS Yesterday, 11:42 PM Go to last post


    it's better to share your workbook, it will be more comprehensive ......

    hossat Yesterday, 10:23 PM Go to last post

    Editing OFT from Excel

    What is the result in the msgbox ?

    [code]Private Sub CommandButton1_Click()
    template = "T:\Coordination des interventions\Coordination...

    snb Yesterday, 09:58 PM Go to last post

    help required on developing a formula

    In order to devise a formula, I need to understand the points at which the calculation is needed. You say it is based on column L, which suggests...

    Hercules1946 Yesterday, 08:57 PM Go to last post

    Help with a formula

    Hello Maria
    I think you have posted your question to an (unrelated) existing thread instead of the correct procedure, which is to start a new one....

    Hercules1946 Yesterday, 08:34 PM Go to last post