Since the economy causes so many business problems, why do your spreadsheets ignore it?

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

    by
    Ken Puls
    Rate this article
    Published on 2006-09-24 10:10 PM      Number of Views: 20378  
    0 Comments Comments
    Categories:
    1. Excel - UDF,
    2. Excel - VBA Programming
    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!

Gravatar by 1e2.it