Mike,
Try this: it list the files and date/times in columns A/B and
sorts them in descending order so that rows 1 & 2 are the latest.
Place this code in a general module in your workbook - it uses first
worksheet (normally Sheet1) to store the results. Change the "With
Worksheet.." statement to suit AND the .Lookin statement .
Execute the FindFile macro.
HTH
Sub FindFile()
Dim FNames() As String, FTimes() As Date
Set fs = Application.FileSearch
With fs
filescount = 0
.LookIn = "C:\Documents and Settings\John\My Documents\My Excel" '<===
CHANGE
.Filename = "*.xls"
.Execute
If .FoundFiles.Count 0 Then
For i = 1 To .FoundFiles.Count
docname = FileNameOnly(.FoundFiles(i))
filescount = filescount + 1
ReDim Preserve FNames(filescount)
ReDim Preserve FTimes(filescount)
FNames(filescount) = docname
FTimes(filescount) = FileDateTime(.FoundFiles(i))
Next i
End If
End With
With Worksheets(1)
For i = 1 To filescount
.Cells(i, 1) = FTimes(i)
.Cells(i, 2) = FNames(i)
Next
.Range("a1:b" & filescount).Sort Key1:=.Range("A1"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function
"Michael Smith" wrote:
Great that helps....how would i then compile a list of all times and
file names that are in a folder?
Would this be a For Each loop?
*** Sent via Developersdex http://www.developersdex.com ***