Results 1 to 8 of 8

Thread: Article: Function To Check If File Or Directory (Folder) Exists

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14

    Article: Function To Check If File Or Directory (Folder) Exists



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    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

  3. #3
    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 ~

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    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.

  5. #5
    terrible terrible method ...... word of warning, Never Ever use "GOTO"

    VERY BAD OLD FASHIONED PRE OBJECT ORIENTATED PRACTICE

  6. #6
    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 : )
    Last edited by sgj; 2014-09-16 at 10:45 AM. Reason: hit enter key too soon

  7. #7
    Thanks Ken. Just joined this forum.

  8. #8
    I found Macro Weakness. If you use as argument empty string, function return: True!.

    Example:
    'Macro Purpose: Test if directory exists
    If FileFolderExists("") Then '<--- Path is empty
    MsgBox "Folder exists!"
    Else
    MsgBox "Folder does not exist!"
    End If

    Result: Folder exists!
    Last edited by manam; 2015-11-16 at 09:32 PM. Reason: Formating

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •