Locate Most Recent File

[andibevan]

Hi All,

I need to be able to locate the most recent file within a specifie
directory so that I can use it for other parts of my code.

Does anyone have any clever ideas on how you can do this?

Thanks

Andy

;

 

[Nikos Yannacopoulos]

Try this piece of code:

Sub Most_Recent_File()
Dim fs, f, f1
Dim vDT As Date
Dim vFName As String
fldr = "C:\Documents\Spreadsheets\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.Files
For Each f2 In f1
If f2.DateLastModified > vDT Then
vFName = f2.Name
vDT = f2.DateLastModified
End If
Next
Debug.Print vFName, vDT
End Sub

HTH,
Nikos

 

[keepITcool]

maybe not clever but it works..

Sub mrf()
Dim szFile(1) As String
Dim dtFile(1) As Date

szFile(0) = Dir("*.xls")

While Not szFile(0) = vbNullString
dtFile(0) = VBA.FileDateTime(szFile(0))
If dtFile(0) > dtFile(1) Then
dtFile(1) = dtFile(0)
szFile(1) = szFile(0)
End If
szFile(0) = Dir()
Wend

MsgBox szFile(1) & " timestamp " & dtFile(1)

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

The filesearch object should be able to help you. Look in Excel VBA help
for details and sample code.