Find newest file in a folder

**duckboy1981** · 10-29-2008, 08:01 AM

Hi all,

I am doing a project for work at the moment where by I have to manipulate a report compiled by an external program and do various bits and bobs to it. Trouble is, the other program creates a new version every time it runs the report (i.e filename.1.xls, and the next day filename.2.xls, and so on). Is there any way of searching the folder these files are in to find the newest file and select that one as the one to open and manipulate? There will be no files other than these in the folder.

Thanks,
David

**royUK** · 10-29-2008, 08:57 AM

Does this help

http://j-walk.com/SS/excel/tips/tip97.htm

**duckboy1981** · 10-29-2008, 09:10 AM

Looks to be just what I am after. I will have a play when I get chance and let you know.

Thanks

**royUK** · 10-29-2008, 09:19 AM

Thanks, let us know & mark the Thread solved when you have checked.

**MickG** · 10-29-2008, 10:07 AM

Hi, This is more or less what you've already got from "RoyUk"
NB:- Change File Path to suit.

Dim f As String, Directory As String
Dim Fdate As Date
Dim Temp As Date, nFile As String
Directory = "C:\documents and settings\test\desktop\excel\"
    f = Dir(Directory)

Do While f <> ""
Fdate = FileDateTime(Directory & f)
  If Not f = ThisWorkbook.name Then
     If Fdate > Temp Then
       Temp = Fdate
       nFile = f
    End If
End If

f = Dir()
Loop
MsgBox nFile & " " & Temp
Workbooks.Open (Directory & nFile)

Regards Mick