Scan Through All Files and Subdirectories

I had a very similiar problem. It was solved by Rylo.
Here's a link to the thread.

HTML Code: 

Within it you will find a files
with the code he setup for me. I think you could probably
use it for your situation. It finds specific files and provide directory paths
w/hyperlinks.

good luck,

BDB

Great link. Thanks a lot, BDB. That answers most of my questions. I still have one remaining question, though. Hopefully someone out there can help me.

Is it possible to return the modified date of each of the files?

TIA

Jason

Does anyone know if this is possible? Thanks.

There are FSO methods to get it. If you use early binding, it makes finding those easy. If you don't know fso, get the help file script56.chm.

For whatever method, you can use VBA's FileDateTime(filename).

For some other fso and dos methods see my post at VBAExpress.

Here is an example FileSearch method. It won't work in 2007.

Option Explicit
Option Base 0


Sub BatchRun()
  Dim f() As Variant, e As Variant
  f() = FindFiles(ThisWorkbook.Path, "BatchTest*.xls", False)
  For Each e In f()
    EditWB e
  Next e
End Sub

Sub EditWB(wbName)
  Dim wb As Workbook
  Set wb = Workbooks.Open(wbName)
  wb.Worksheets(1).Range("A1").Value = wb.Name
  wb.Close True
End Sub

Sub BigFiles()
  Dim f() As Variant, e As Variant, bigf() As Variant, i As Long
  
  f() = FindFiles(ThisWorkbook.Path, "*.xls")
  i = -1
  For Each e In f()
        'Get files larger than 20 kb
        If FileLen(e) / 1000 > 25 Then
          i = i + 1
          ReDim Preserve bigf(i)
          bigf(i) = e
        End If
  Next e
  
  If bigf(0) = "" Then Exit Sub
  Range("A:A").ClearContents
  Range("A1").Resize(UBound(bigf) + 1) = bigf()
End Sub

Function FindFiles(sRootFolder As String, sFiles As String, _
  Optional searchSubFolders As Boolean = True) As Variant
     
    Dim fs As Object
    Dim strFilename As String
    Dim i As Long, LastRow As Long
    Dim a() As Variant
     
    Set fs = Application.FileSearch
    With fs
        .LookIn = sRootFolder
        .Filename = sFiles 'set your filename or extension with wilcards if needed.
        .searchSubFolders = searchSubFolders
        LastRow = .FoundFiles.Count
        If .Execute() > 0 Then
            For i = 1 To LastRow
                strFilename = .FoundFiles(i)
                ReDim Preserve a(i - 1)
                a(i - 1) = strFilename
            Next i
        Else
            MsgBox "No " & sFiles & " in " & sRootFolder & " found!", vbCritical
            End
        End If
    End With
    
    FindFiles = a()
     
End Function

Thanks so much for the info, Kenneth. I am very unfamiliar with FSO, so thank you for the help file. I need to do some reading now.

The property for which I was looking was DateLastModified.

Jason

If the fso help file does not display everything, close and right click it and Unblock it. From the help file:

Function ShowFileAccessInfo(filespec)
   Dim fso, f, s
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.GetFile(filespec)
   s = UCase(filespec) & "<BR>"
   s = s & "Created: " & f.DateCreated & "<BR>"
   s = s & "Last Accessed: " & f.DateLastAccessed & "<BR>"
   s = s & "Last Modified: " & f.DateLastModified
   ShowFileAccessInfo = s
End Function

The API method from the API Guide:

Private Const OPEN_EXISTING = 3
Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type
Private Type BY_HANDLE_FILE_INFORMATION
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    dwVolumeSerialNumber As Long
    nFileSizeHigh As Long
    nFileSizeLow As Long
    nNumberOfLinks As Long
    nFileIndexHigh As Long
    nFileIndexLow As Long
End Type

Private Declare Function GetFileInformationByHandle Lib "kernel32" (ByVal hFile As Long, lpFileInformation As BY_HANDLE_FILE_INFORMATION) As Long
Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, lpSecurityAttributes As Any, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Sub Form_Load()
    'KPD-Team 2001
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@allapi.net
    Dim hFile As Long, FileInfo As BY_HANDLE_FILE_INFORMATION
    'create a handle to the file 'c:\autoexec.bat'
    hFile = CreateFile("c:\autoexec.bat", 0, 0, ByVal 0&, OPEN_EXISTING, 0, ByVal 0&)
    'retrieve the file information
    GetFileInformationByHandle hFile, FileInfo
    'close the handle
    CloseHandle hFile
    'show the result
    MsgBox "File size: " + CStr(FileInfo.nFileSizeLow), vbInformation
End Sub