VBA functon to check if specific files exist in directory

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

Who is Participating?
dlmilleConnect With a Mentor Commented:
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: http://www.ozgrid.com/forum/showthread.php?t=44778&page=1

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.


An alternative, which might be a bit simpler.
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

Open in new window

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Michael FowlerSolutions ConsultantCommented:
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

Michael FowlerSolutions ConsultantCommented:
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

mato01Author Commented:
This works perfectly.  Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.