• 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.

     

    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 3 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
      Loop
      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.