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"
Else
MsgBox sFile & " does not exist"
End If

Open in new window

mato01Asked:
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
        Else
            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.

Cheers,

Dave
fileInfo-r1.xls
0
 
dlmilleCommented:
An alternative, which might be a bit simpler.
 
'Source:http://www.excelguru.ca/node/30
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

Open in new window


Dave
0
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

Michael
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"
      Else
         Range("B" & i).Value = "Does not exist"
      End If
   Next
End sub

Open in new window

0
 
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"
      Else
        FileExists = "Does not exist"
      End If
   Next
End Function

Open in new window

0
 
mato01Author Commented:
This works perfectly.  Thanks
0
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.