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

;

--
andibeva
-----------------------------------------------------------------------
andibevan's Profile: http://www.excelforum.com/member.php...nfo&userid=988
View this thread: http://www.excelforum.com/showthread.php?threadid=27274

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

"andibevan" wrote in message
...

Hi All,

I need to be able to locate the most recent file within a specified
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

;)


--
andibevan
------------------------------------------------------------------------
andibevan's Profile:
http://www.excelforum.com/member.php...fo&userid=9882
View this thread: http://www.excelforum.com/showthread...hreadid=272742

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


andibevan wrote:


Hi All,

I need to be able to locate the most recent file within a specified
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

;)

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

--
Regards,
Tom Ogilvy

"andibevan" wrote in message
...

Hi All,

I need to be able to locate the most recent file within a specified
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

;)


--
andibevan
------------------------------------------------------------------------
andibevan's Profile:
http://www.excelforum.com/member.php...fo&userid=9882
View this thread: http://www.excelforum.com/showthread...hreadid=272742