Hi,

I need to open the 2 most recent files in a spcific folder. The files will
have the same name, but will also have the date "yy_mm_dd" as part of the
file name. The file with todays date will obviously be the most recent,
however there is no guarantee that the next most recent has the previous
working days date.

How do I open the 2nd most recent file?

Thanks
Richard

This lists the two file names. You can use those names to open the files.
Note that an open file will have the current date.
'--
Sub TheTwoLatestFiles()
'Jim Cone - San Francisco, USA - June 2005
'Jim Cone - Portland Oregon - June 2008. Modified to show additional file.
'Displays the two latest file names in the strPath folder.

Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strPath As String
Dim strName_1 As String
Dim strName_2 As String
Dim dteCreated_1 As Date
Dim dteCreated_2 As Date

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office11\Library"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified dteCreated_1 Then
dteCreated_1 = objFile.DateLastModified
strName_1 = objFile.Name
ElseIf objFile.DateLastModified dteCreated_2 Then
dteCreated_2 = objFile.DateLastModified
strName_2 = objFile.Name
End If
Next 'objFile

' Display file names in message box.
MsgBox strName_1 & " " & Format(dteCreated_1, "Short Date") & vbCr _
& strName_2 & " " & Format$(dteCreated_2, "short Date") _
& vbCr & "are the two latest files ", , "Blame Jim Cone"

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Richard"
wrote in message
Hi,
I need to open the 2 most recent files in a spcific folder. The files will
have the same name, but will also have the date "yy_mm_dd" as part of the
file name. The file with todays date will obviously be the most recent,
however there is no guarantee that the next most recent has the previous
working days date.
How do I open the 2nd most recent file?
Thanks
Richard

Jim

That is great thanks for your help

Cheers
Richard

"Jim Cone" wrote:

This lists the two file names. You can use those names to open the files.
Note that an open file will have the current date.
'--
Sub TheTwoLatestFiles()
'Jim Cone - San Francisco, USA - June 2005
'Jim Cone - Portland Oregon - June 2008. Modified to show additional file.
'Displays the two latest file names in the strPath folder.

Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strPath As String
Dim strName_1 As String
Dim strName_2 As String
Dim dteCreated_1 As Date
Dim dteCreated_2 As Date

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office11\Library"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified dteCreated_1 Then
dteCreated_1 = objFile.DateLastModified
strName_1 = objFile.Name
ElseIf objFile.DateLastModified dteCreated_2 Then
dteCreated_2 = objFile.DateLastModified
strName_2 = objFile.Name
End If
Next 'objFile

' Display file names in message box.
MsgBox strName_1 & " " & Format(dteCreated_1, "Short Date") & vbCr _
& strName_2 & " " & Format$(dteCreated_2, "short Date") _
& vbCr & "are the two latest files ", , "Blame Jim Cone"

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Richard"
wrote in message
Hi,
I need to open the 2 most recent files in a spcific folder. The files will
have the same name, but will also have the date "yy_mm_dd" as part of the
file name. The file with todays date will obviously be the most recent,
however there is no guarantee that the next most recent has the previous
working days date.
How do I open the 2nd most recent file?
Thanks
Richard

Jim

There seems to be some issue with this not picking up one of the files. Is
there a way of comparing the file names, which contain the date
received/created, rather than using the system modified date?

Thanks
Richard

"Jim Cone" wrote:

This lists the two file names. You can use those names to open the files.
Note that an open file will have the current date.
'--
Sub TheTwoLatestFiles()
'Jim Cone - San Francisco, USA - June 2005
'Jim Cone - Portland Oregon - June 2008. Modified to show additional file.
'Displays the two latest file names in the strPath folder.

Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strPath As String
Dim strName_1 As String
Dim strName_2 As String
Dim dteCreated_1 As Date
Dim dteCreated_2 As Date

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office11\Library"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified dteCreated_1 Then
dteCreated_1 = objFile.DateLastModified
strName_1 = objFile.Name
ElseIf objFile.DateLastModified dteCreated_2 Then
dteCreated_2 = objFile.DateLastModified
strName_2 = objFile.Name
End If
Next 'objFile

' Display file names in message box.
MsgBox strName_1 & " " & Format(dteCreated_1, "Short Date") & vbCr _
& strName_2 & " " & Format$(dteCreated_2, "short Date") _
& vbCr & "are the two latest files ", , "Blame Jim Cone"

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Richard"
wrote in message
Hi,
I need to open the 2 most recent files in a spcific folder. The files will
have the same name, but will also have the date "yy_mm_dd" as part of the
file name. The file with todays date will obviously be the most recent,
however there is no guarantee that the next most recent has the previous
working days date.
How do I open the 2nd most recent file?
Thanks
Richard