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:
<tbody>
</tbody>
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:
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 |
<tbody>
</tbody>