VBA functon to check if specific files exist in directory

Posted on 2011-10-16
Last Modified: 2012-06-21
Trying to find code that would allow me to run VBA code to check to see if a list of 30 files are in a directory, that gives me the user ID, date, and time the file was saved to the directory. I found the attached VBA, but it only checks one file.  
sFile = "c:\Temp\Test.txt"
If File_Exists(sFile) = True Then
MsgBox sFile & " exist"
MsgBox sFile & " does not exist"
End If

Open in new window

Question by:mato01
    LVL 15

    Expert Comment

    LVL 41

    Expert Comment

    An alternative, which might be a bit simpler.
    Public Function FileFolderExists(strFullPath As String) As Boolean
    'Author       : Ken Puls (
    '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

    Open in new window

    LVL 23

    Expert Comment

    If you place the file list into a worksheet you can then loop through the range and test each file.

    The attached sub assumes the list of file names is in column A starting at row 1. It determines the last row and loops through each file displaying the result in column B

    sub testFiles()
       dim i as long
       For i = 1 to Range("A" & Cells.count).end(XlUp).row
          If File_Exists(Range("A" & i).Value) = True Then
             Range("B" & i).Value = "Exists"
             Range("B" & i).Value = "Does not exist"
          End If
    End sub

    Open in new window

    LVL 23

    Expert Comment

    You could also create a function and then enter it in column B using dragging it down to get the result for each file
    Function FileExists(rng as Range) as String
          If File_Exists(rng.Value) = True Then
             FileExists = "Exists"
            FileExists = "Does not exist"
          End If
    End Function

    Open in new window

    LVL 41

    Accepted Solution

    All of us are sorely mis-reading the original question - appears the OP wants whether the file exists, the user ID, date, and time the file was saved to the directory.

    Probably can finangle the same out of the FSO object, but I've been using this set of functions for a while which work for me.

    Here's the link to my source:

    It requres a reference to the Microsoft Shell Controls and Automation library.

    Here's the primary code:

    Const attribs = "File,Exists,Owner,Author,DateModified"
    Sub getFileProperties()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim i As Long
    Dim hdr As Variant
    Dim fName As String, folderName As String
        Set wkb = ActiveWorkbook
        Set wks = wkb.ActiveSheet
        folderName = ActiveWorkbook.Path 'change this to the focus folder name, if needed
        'Assumes list of files start in Column A, Row 2
        hdr = Split(attribs, ",")
        wks.Range("A1").Resize(, UBound(hdr) + 1).Value = hdr
        For i = 2 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row
            fName = folderName & "\" & wks.Range("A" & i).Value
            If FileFolderExists(fName) = True Then
                wks.Range("B" & i).Value = "Exists"
                wks.Range("C" & i).Value = GetFileAttributes(fName).Owner
                wks.Range("D" & i).Value = GetFileAttributes(fName).Author
                wks.Range("E" & i).Value = GetFileAttributes(fName).DateModified
                wks.Range("B" & i).Value = "Does not exist"
            End If
        Next i
    End Sub

    Open in new window

    The attached workbook should be self-explanatory.  Author is sometimes not populated with saved files, so usage may vary.  Also, owner can be a user id and/or it can be a user group - again, usage may vary.  In tests with my own files, I was satisfied with the results, so this should work "out of the gate".

    Just copy your files and paste them on the first sheet - directions are provided.



    Author Closing Comment

    This works perfectly.  Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now