VBA code to open last modified XLS file from yearly/monthly sub folders

[ct2911]

Hello everyone,

I've been trying to code a macro to open the latest modified file from various yearly/monthly sub folders.

E.g. G:\Support\reports\2014\september\checklist15.xls

I have managed to get hold of the below code from the thread - http://www.mrexcel.com/forum/excel-questions/647157-open-last-saved-excel-file-folder-subfolder-visual-basic-applications-excel-2010-a.html

There are no errors when I run the code but when I create a new sample workbook inside one of the folders to test the macro pulls through the latest modified file it pulls through the previous latest modified file.

Thank you in advance for your help and thank you everyone on the forum for all the previous code provided.

Code:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dim FileSys As Object
Dim objFile As Object
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
'Set const on next line to your folder path
Const myDir As String = "G:\Support\reports\2014\september\checklist15.xls"

'set up filesys objects
Set FileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSys.GetFolder(myDir)

'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
'Debug.Print objFile.Name
If InStr(1, objFile.Name, ".xls") > 0 Then
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
End If
Next objFile
Workbooks.Open myDir & Application.PathSeparator & strFilename

Set FileSys = Nothing
Set myFolder = Nothing
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BiocideJ]

I didn't see anything too off with the code. However, I did notice that if there was an open file (or a file that didn't close properly), it would try to open up the auto-backup file for it. I added code to ignore workbooks beginning with the ~ symbol so maybe that was what your problem was.
Also, I made the Const declaration a normal variable and excluded the file name since you are using the folder portion anyway.

Dim FileSys As Object
Dim objFile As Object
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
Dim myDir As String
    'set path for files - change for your folder
    'Set const on next line to your folder path
    myDir = "G:\Support\reports\2014\september\"
    
    'set up filesys objects
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FileSys.GetFolder(myDir)
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        'Debug.Print objFile.Name
        If InStr(1, objFile.Name, ".xls") > 0 And Left(objFile.Name, 1) <> "~" Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    Workbooks.Open myDir & Application.PathSeparator & strFilename
    
    Set FileSys = Nothing
    Set myFolder = Nothing

 

[ct2911]

BiocideJ, thanks so much for the reply. I will try this tomorrow morning at work to see if it works. I forgot to mention I'm running excel 2007 if it makes any difference. If I wanted the code to look within all the sub folders of "reports" would I simply delete the"2014\september" from myDir ? Basically I need it to find the latest XLS file without recoding it each year/month every time a new sub folder ("2015") ("October") is created.

I didn't see anything too off with the code. However, I did notice that if there was an open file (or a file that didn't close properly), it would try to open up the auto-backup file for it. I added code to ignore workbooks beginning with the ~ symbol so maybe that was what your problem was.
Also, I made the Const declaration a normal variable and excluded the file name since you are using the folder portion anyway.

Dim FileSys As Object
Dim objFile As Object
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
Dim myDir As String
    'set path for files - change for your folder
    'Set const on next line to your folder path
    myDir = "G:\Support\reports\2014\september\"
    
    'set up filesys objects
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FileSys.GetFolder(myDir)
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        'Debug.Print objFile.Name
        If InStr(1, objFile.Name, ".xls") > 0 And Left(objFile.Name, 1) <> "~" Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    Workbooks.Open myDir & Application.PathSeparator & strFilename
    
    Set FileSys = Nothing
    Set myFolder = Nothing

 

[ah2hamed]

Dear BiocideJ

thanks for your support it is really help me too much.

but i need other help

1- myDir to be a dynamic depend on cell value or formula
2- i want to use the same code for other command button to open the folder
3- objFile.Name, ".xls" (it could be any file not only the excel files)

Thanks
Ahmed