VBA open most recent file in folder with specific string in filename

[JPM]

I am looking for some code that would open the most recent file in a folder if the file name starts with a specified string (e.g. Testfileyyymmdd.xlsx)

I found this code that opens the most recent file, but does not take into account the filename:

Sub GetMostRecentFile()
'To use FileSystemObject goto Tools > References and check "Microsoft Scripting Runtime"

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - change for your folder
Const myDir As String = "J:\Dailies\Test MTD"

'set up filesys objects
Set FileSys = New 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
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile

'Open Workbook
Workbooks.Open "J:\Dailies\Test MTD\" & strFilename
Set FileSys = Nothing
Set myFolder = Nothing
End Sub


Can anyone help?

 

[Jerry Sullivan]

Hi JPM,

Try adapting the code in Post #44 of this thread.
http://www.mrexcel.com/forum/excel-questions/647157-open-last-saved-excel-file-folder-subfolder-visual-basic-applications-excel-2010-a-2.html

 

[JPM]

Jerry,
Thank you very much for the link. I actually looked at some of your other similar postings but didn't see this one.
I tried it out and it will work for me. However, I have to say, with the two functions it looks much more complicated than I expected.

Thanks again.

 

[Vinnyjuice]

Hi Jerry, That link doesn't exists. Kindly guide me with the code.