VBA functon to check if specific files exist in directory

Posted on 2011-10-16
Medium Priority
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 42

Expert Comment

ID: 36975534
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

LVL 23

Expert Comment

by:Michael Fowler
ID: 36975644
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Expert Comment

by:Michael Fowler
ID: 36975649
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 42

Accepted Solution

dlmille earned 2000 total points
ID: 36975707
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.



Author Closing Comment

ID: 36976379
This works perfectly.  Thanks

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

807 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