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

 

[vbanoob12]

Hi Sal and Welcome to the Board,

Here's some code you can try...

Sub OpenLatest()
'---Opens a sheet based on date, searches backward from today til it finds a matching date

    Dim dtTestDate As Date
    Dim sStartWB As String
    
    Const sPath As String = "C:\TEST\"
    Const dtEarliest = #1/1/2010#  '--to stop loop if file not found by earliest valid date.

    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name
    
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & "Report Name " & Format(dtTestDate, "(YYYY-MM-DD)") & ".xlsm"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
    
    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
End Sub

You can remove the parenthesis from "(YYYY-MM-DD)" if those are not literally in your filename.

Sorry to bump an old thread. I need something like this but much simpler.

1)My filenames start with the date (4 characters) ie. 091015. Right in the begining.
2)I don't need to open the file. I just need the macro to find the oldest date in the folder and tell it to me in say cell A1.

How would I go about editing this?
Thank you kindly for your assistance!

 

[vbanoob12]

can anybody help with this?

 

[Jerry Sullivan]

Hi vbanoob12,

This code should find that filename and store it in a variable.

Sub FindFilenameWithEarliestDateCode()
 '--looks for files with pattern "MMDDYY*.xls*"
 '  returns the file matching that pattern that represents the
 '  earliest date.

 Dim sCurrFile As String, sEarliestFilename As String
 Dim lCurrNbr As Long, lMinNbr As Long
 
 Const sPATH = "C:\Test\" 'edit with your path
 
'--start with max 6 digit number
 lMinNbr = 999999
 
 sCurrFile = Dir(sPATH & "*.xls*")

 While sCurrFile <> ""
   If sCurrFile Like "######*.xls*" Then
      lCurrNbr = CLng(Mid(sCurrFile, 5, 2) & Left(sCurrFile, 4))
      If lCurrNbr < lMinNbr Then
         '--found filename with earlier date code
         lMinNbr = lCurrNbr
         sEarliestFilename = sCurrFile
      End If
   End If
   sCurrFile = Dir()
 Wend
 If Len(sEarliestFilename) Then
   MsgBox "The file with the earliest date code is: " & sEarliestFilename
 Else
   MsgBox "No files matching pattern found"
 End If
End Sub

Note the code does not attempt to validate that the date codes represent possible dates.
A filename starting with "004400" could be returned as the earliest date code. That validation could be added, but that adds complexity that isn't needed unless you might have filenames with that non-conforming pattern in your folder.

 

[hzarry]

Hi,
I am trying to adapt the above code so that it also takes into account a timestamp. I want the code to do exactly the same as above, as in open the earliest version of the file but the naming format of the file also has a timestamp.

"FileName "& (Date, "DD.MM.YYYY") & " " & Format(Time, "HHMMSS") & ".xlsx"

so in some way is it possible to incoprporate a dtTestTime rule?

Any help would be really appreciated.

Thanks

 

[Jerry Sullivan]

Hi hzarry,

Since you mention "dtTestTime", you must be referring to the code in Post #8 or earlier.

The approach used in that code of trying to open a file with each Date pattern until a match is found is inefficient and I wouldn't recommend it. I was responding to the OP's question about how to modify some code they found in a different thread that used that approach.

The approach by trying to open a file with each Date-Hour-Minute-Second pattern until a match is found would be unfeasibly slow.

The code shown in Post #13 could be adapted to do what you describe.

A few clarifications....
1. Do you want the earliest pattern file (as you wrote) or the latest pattern file (like the previous examples)?

2. It the prefix portion of the filename (the part you showed as "FileName ") fixed so it can be written into the code? Or are you trying to compare files with different prefixes in the same folder to find the latest or earliest version of any filename with a datestamp?

 

[hzarry]

Hi Jerry,
Thanks for your quick reply.

First of all, I meant to say the latest version and not the earliest version, my mistake.

Secondly, yes the prefix of my filename is always "Daily Exception Report" folowed by DD.MM.YYYY HH.MM.SS.

What I am trying to achieve with this macro is however is to open up yesterday's report and then use a Vlookup to copy over relevant data from yesterday's report to today's report. So even if i have a suitable method for opening the yesterday's version of the report, i'm not sure how I could create a Vlookup in a macro that would distinguish the reports from each other, when the prefixes are the same.

I hope that makes sense.

 

[Jerry Sullivan]

If you are just adding a Vlookup formula to the Active Workbook that references yesterday's report, then you don't need to open yesterday's workbook.

What is an example formula that you get when you enter that formula manually in the ActiveWorkbook then close yesterday's workbook?

What is the range of cells in the ActiveWorkbook in which you enter this formula?

 

[emoandy05]

JERRY!

The original code posted work great for me! Currenly, I get notifications that pop up until the macro finds a matching date, every time i have to click ok and it continues the process. Anyway for this notification not to appear and just run until it finds a match or hits the dtEarliest date?

Thanks.

Emerson

 

[Jerry Sullivan]

Hi Emerson, I've responded to the thread you started here since it includes the code you are trying.

http://www.mrexcel.com/forum/excel-questions/905930-set-const-dynamic-variable-date.html

 

[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.