Opening the lastest file

[b_abdelhamid]

Hi all,

do any one of you know how to open the latest recorded .xls file in a
given folder? In fact, in my case my files all have a date in their
name (corresponding to the date they have been created), unfortunately
this is not always done on a daily basis. But I always need to open the
two latest files.

thx for your help,
cheers,

BigBen

 

[b_abdelhamid]

Just in case (I forgot to precise), what I need is a VBA code to
include in a procedure

BigBen

 

[Guest]

this pseudo code should give you a start.

dim dt as Date, sPath as String
Dim sName as String
Dim s1 as String, s2 as String
Dim bk1 as Workbook, bk2 as Workbook
sPath = "C:\myfolder\"
sName = dir(sPath & "*.xls")
do while sname <> ""
' parse out the date or use filedatetime
dt = filedatetime(sPath & sName)
if dt > maxDate then
if maxDate > max2ndDate then
max2ndDate = maxDate
s2 = s1
end if
maxDate = dt
s1= sName
elseif dt > Max2ndDate then
Max2ndDate = dt
s2 = sName
end if
sName = dir
Loop
if s1 <> "" then
set bk1 = Workbooks.open(spath & s1)
end if
if s2 <> "" then
set bk2 = workbooks.open(spath & s2)
end if

 

[b_abdelhamid]

Thx for your help Tom,

I managed to find another way in another group:


Sub MostRecentFile()

With Application.FileSearch
..NewSearch
..LookIn = "\\jcfilc01\group\energy\Pricing Models\Curve Generation\"
..Filename = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderDescending) > 0 Then
Workbooks.Open .FoundFiles(1)
Else
MsgBox "No can do!"
End If
End With

End Sub


Cheers,

BB

 

[Guest]

You might want to test it before you rely on it. I have heard that Fileseach
can be flakey and also that msoSortOrderDescending doesn't work. If it
works for you, that is the easiest, but as I said.

 

[b_abdelhamid]

Well it seems to work! the last modified file is opened. However your
suggested program allows me to go further and to open the 2nd modified
files in the list.

 

[Guest]

so would the other

Workbooks.Open .FoundFiles(1)
Workbooks.Open .FoundFiles(2)