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

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
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

New Member
Joined
Dec 9, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Jerry, That link doesn't exists. Kindly guide me with the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top