VBA to open the most recent Excel file in a directory 1

[LandOfHornbill]

Greetings!

I searched for days in many VBA websites but can't seem to find an appropriate and usable code on how to automatically open up a most recently modified file in a given directory. The xls filename will never be the same in every each other day and hence such a vba code is important. Greatly appreciate if someone can give me some help here. Thanks in advance.

 

[SkipVought]

Hi,

Check out the DateLastModified property of the File Object in FileSystemObject.

Skip,

_[red][/red]

 

[Swi]

Something like this should work:

The below code is taken from here:

http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0609.mspx

Private Sub Command1_Click()
Dim objExcel As New Excel.Application
Dim fso As New FileSystemObject
Dim objSearch As Variant
Dim PreviousDate As Date
Dim PreviousFile As String

objExcel.Visible = True
Set objSearch = objExcel.FileSearch
objSearch.Lookin = "C:\Test"
objSearch.SearchSubfolders = True
objSearch.FileName = "*.xls"
objSearch.Execute

' Get the first file for the compare
For Each strFile In objSearch.FoundFiles
    PreviousFile = strFile
    PreviousDate = fso.GetFile(strFile).DateLastModified
    Exit For
Next

' Check to see what file has been modified most recently
For Each strFile In objSearch.FoundFiles
    If fso.GetFile(strFile).DateLastModified > PreviousDate Then
        PreviousFile = strFile
        PreviousDate = fso.GetFile(strFile).DateLastModified
    End If
Next

objExcel.Workbooks.Open PreviousFile
MsgBox "Check Excel For The Most Recently Modified File!", vbInformation
objExcel.Quit
Set objExcel = Nothing
End Sub

Swi

 

[Swi]

Oops, forgot a Set fso = Nothing at the end of the sub.

Swi

 

[HughLerwill]

If you would like to avoid the fso then checkout the FileDateTime and Dir$ functions which are native to vba/ vb6.

regards Hugh