Open File With Latest Date

So, you would want to take TODAY as a starting date, construct a path and filename based on today's date, and see if that file exists? If so, open it. If not, go back one day, repeat until you find the most recent existing file?

Something like this:

Sub GetEmployeeCount()
Dim sOpenLastReport As String, Cnt As Long, wb As Workbook

Application.ScreenUpdating = False

Do
    sOpenLastReport = "H:\Desktop\BA.com Daily Production Reports\" & _
                      Format(Date - Cnt, "MM-YY") & "\BA.com_ProductionReport_" & _
                      Format(Date - Cnt, "MM-DD-YY") & ".xlsm"
    
    If Len(Dir(sOpenLastReport)) > 0 Then Exit Do
    Cnt = Cnt + 1
    If Cnt Mod 10 = 0 Then
        If MsgBox("We've checked " & Cnt & " dates and not found a file, keep going backwards?", vbYesNo, "CONTINUE") = vbNo Then Exit Sub
    End If
Loop
    
Set wb = Workbooks.Open(sOpenLastReport)

wb.Sheets("EmployeeCount").Range ("B4:Z17").Copy
ThisWorkbook.Sheets("EmployeeCount").Range("C4").PasteSpecial xlPasteValues

wb.Sheets("EmployeeCount").Range("B35:Z35").Copy
ThisWorkbook.Sheets("EmployeeCount").Range("C35").PasteSpecial xlPasteValues

wb.Close False
Application.ScreenUpdating = True

Exit Sub

It's counting how many times we've been through the DO/LOOP before and subtracting that number from today's date to adjust the next date checked by that many days.

First time through, CNT=0, so the date checked is TODAY-0
Second time through CNT=1, so the date checked is TODAY-1
and so on... until a date is checked and a file found.

I put in an escape point, every time it unsuccessfully checks 10 dates, it stops and asks if you want to keep going.

sOpenLastReport is a string variable. At the beginning of the DO we construct a string using today's date.

Then we use the DIR(string) method to bring back the same filename from the hard drive. DIR = Directory, I guess. So it's basically saying, go get the hard drive name of this file using this string.

If the file is not there, the DIR result is blank, NULL, so the LEN() of that result is also 0. But if the file WAS found, the result coming back will be the file's name, again, and anything that happens to be will be longer than 0. It's a poor man's "does this file exist" test.

When it comes back when a LEN > 0, the loop is aborted and we are off to open the found file.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks, see you around the forum!