Macro to open an excel file based on the latest date found in filename

[salasv]

Hoping I can get assistance with something I've seen lots of various solutions for, but none of them meet my needs.

I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.

There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.

I found this previous thread with the same problem, but since it was a couple years old, I thought I'd better start a new thread.
http://www.mrexcel.com/forum/excel-...el-file-based-latest-date-found-filename.html

The solution in this thread was to provide a starting date to work backwards from, which I don't want to do. The option of counting backwards from today would be good, but I'm not well-versed enough in VBA to make this work on my own. I may also be opening the file the same day it was created, if this matters.

Thanks in advance for your assistance!

Cheers!

Sal

 

[javijuan]

Hello Jerry... thanks for all your replies here... this is the only thread I have found with situations similar to mine. I know this is an old thread, but I can't find anything else...

Im using your code from post #8

my situation is that the person that update the excel on a sharepoint... sometimes add more information to the end of the filename... example Master Report 7.20.18 v1.2.xlsx

my code has: sPath & File_Name_ & Format(dtTestDate, "mm.dd.yy") & "" & ".xlsx"

it works great if I add the v1.2 after the date and before .xlsx, but I want to know if there is a way of adding anything in between. I have try adding "*" or "File_Name_" but it does not work. Any help will be greatly appreciated... Thanks.

Bumping this... I still have the issue 3 months later... I cannot find the solution. Please Help.