Last modified search for folder and all subfolders 1

[UnsolvedCoding]

I have seen a lot of stuff on how to run a lastmodified search on a folder, but am kind of stumped on how to run it to include a search of subfolders and search only for .xl* files.

Some of the folders have subfolders that go 10 deep and to run all the folders one at a time would be just north of lunacy.

So far I am having no luck even with SearchSubFolders = True

Any suggestions?

 

[PHV]

What is your actual code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

At this point I am not certain if I need to use a list of paths from a worksheet or will be going straight off the drive.

Searching the subfolders is where I get stuck.

Sub fileList()

Dim Test1 As String

Dim I As Long

Dim f

Dim myFS As Scripting.FileSystemObject
Set myFS = New Scripting.FileSystemObject
Dim myFolder As Folder

Set myFolder = myFS.GetFolder("J:\Test")

I = 1

For Each f In myFolder.Files

If Right(f.Name, 4) Like "*xl*" And f.DateLastModified >= #1/15/2012 12:00:01 AM# Then

''f.SearchSubFolders = True

ThisWorkbook.Sheets("Sheet1").Range("A" & I).Value = f.Name
ThisWorkbook.Sheets("Sheet1").Range("B" & I).Value = f.DateLastModified
ThisWorkbook.Sheets("Sheet1").Range("C" & I).Value = f.Path

I = I + 1

End If

Next

End Sub

 

[PHV]

Searching the subfolders is where I get stuck.
So, use a recursive procedure like this:

Sub fileList()
Dim myFS As Scripting.FileSystemObject
Set myFS = New Scripting.FileSystemObject
recursivefileList myFS, myFS.GetFolder("J:\Test"), 1
End Sub
Sub recursivefileList(myFS As FileSystemObject, myFolder As Folder, I As Long)
Dim f As File, sf As Folder
For Each f In myFolder.Files
  If Right(f.Name, 4) Like "*xl*" And f.DateLastModified >= #1/15/2012 12:00:01 AM# Then
    ThisWorkbook.Sheets("Sheet1").Range("A" & I).Value = f.Name
    ThisWorkbook.Sheets("Sheet1").Range("B" & I).Value = f.DateLastModified
    ThisWorkbook.Sheets("Sheet1").Range("C" & I).Value = f.Path
    I = I + 1
  End If
Next
For Each sf In myFolder.SubFolders
  recursivefileList myFS, sf, I
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

The code works great - thanks!

Modifying it to fit the real path is simple. However I do have one more question.

The imported list I have to scan shows a path with a specific file name on the end of it - for example J:\Test\Book1.xlsx

All together there are around 150,000 files that need to be checked for the last modified date. Can I get the last modified date for a specific file or am I limited to scanning a folder?

Reason for asking - the imported list shows the complete path to the Excel doc, and it would be much faster to work with just that file rather than scanning the folder and scrubbing it against the list.