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

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

BigBen

wrote:
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

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

--
Regards,
Tom Ogilvy



" wrote:

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

BigBen

wrote:
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

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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy



" wrote:

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

BigBen

wrote:
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

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.

--
Regards,
Tom Ogilvy


" wrote:

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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy



" wrote:

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

BigBen

wrote:
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

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.


Tom Ogilvy wrote:
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.

--
Regards,
Tom Ogilvy


" wrote:

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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy



" wrote:

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

BigBen

wrote:
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

so would the other

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

--
Regards,
Tom Ogilvy


" wrote:

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.


Tom Ogilvy wrote:
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.

--
Regards,
Tom Ogilvy


" wrote:

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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy



" wrote:

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

BigBen

wrote:
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