Opening an excel file with the most recent date name in the file

Question

I run a report on a daily basis called "Contract Values UK - dd-mm-yy"

where dd-mm-yy represents the day month and year the report was run.

I've tried the below code but this seems unable to locate the file.

Can someone help me adapt the below code - many thanks.

 Sub OpenLatest()
 a matching date

Dim dtTestDate As Date
Dim sStartWB As String

Const sPath As String = "C:\Users\Documents\Weekly Contract Values Analysis\"
Const dtEarliest = #1/1/2018#  

  dtTestDate = Date
  sStartWB = ActiveWorkbook.Name

  While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
    On Error Resume Next
    Workbooks.Open sPath & "Contract Values UK - " & Format(dtTestDate, "(DD-MM-YY)") & ".xlsm"
    dtTestDate = dtTestDate - 1
    On Error GoTo 0
 Wend

 If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
 End Sub

Possible duplicate of [finding latest file in a folder and opening it (vba access)](https://stackoverflow.com/questions/25490868/finding-latest-file-in-a-folder-and-opening-it-vba-access). The approach is the same instead of `FileDateTime` check the date in your filename which you can extract from the filename with something like `FileNameDate = Mid$(file, Len(file)-13, 8)`. Also see [DateSerial function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateserial-function) — Pᴇʜ, Jan 22 '19 at 10:48
@peh yes, it's kinda the same principle (didn't find that earlier oops) though i don't really understand the code in all honesty, i've tried to update and tailor to my problem but was unable to get anything to work. i've updated my code to my latest solution but cannot get excel to locate the file :( are you able to tell me where i've gone wrong then i'll mark this as a duplicate. — PGD15, Jan 22 '19 at 11:17
@Pᴇʜ i also included an image of the file name encase i messed up in the path ways etc — PGD15, Jan 22 '19 at 11:22
Do you want to look for the recent date in the "File Name" or "File Properties"? — Siddharth Rout, Jan 22 '19 at 11:31
Not sure why you are checking `ActiveWorkbook.Name = sStartWB` in your `IF` condition because that will always be the same as in the line before you assign it as such?. I would use `DIR` to go through all files in your folder and then open the ones you want — Zac, Jan 22 '19 at 11:32
@SiddharthRout file name (see image at top that the format of my file names) - properties in theory should work too but if i ever go in to update the code in contract values report i could mess up my current report. — PGD15, Jan 22 '19 at 11:36
@Zac i believe the logic (probably wrong) is that this will then stop my macro from opening multiple files once 1 file is open in theory it should break the loop. — PGD15, Jan 22 '19 at 11:37

score 1 · Accepted Answer · answered Jan 22 '19 at 11:40

1

Is this what you are trying? (Untested)

I am assuming the file name is like Contract Values UK - dd-mm-yy.xlsm

Const sPath As String = "C:\Users\Documents\Weekly Contract Values Analysis\"
Const dtEarliest = #1/1/2018#

Sub Sample()
    Dim i As Long
    Dim dt As Date: dt = Date
    Dim flName As String, dtPart As String

    '~~> Loop through dates in reverse
    For i = dt To dtEarliest Step -1
        dtPart = Format(i, "dd-mm-yy")
        '~~> Create your file name
        flName = "Contract Values UK - " & dtPart & ".xlsm"

        '~~> Check if exists
        If Dir(sPath & flName) <> "" Then
            MsgBox sPath & flName '<~~ You can now work with this file
            Exit For
        End If
    Next i
End Sub

answered Jan 22 '19 at 11:40

Siddharth Rout

147,039
17
206
250

Yes, this returns the file link but how do i then open the file :') – PGD15 Jan 22 '19 at 11:43
file name is in the question now, there is a picture of the file (with today's date as an example). i just either can't locate the file or open the file :'( – PGD15 Jan 22 '19 at 11:44
Simply replace `MsgBox sPath & flName` by `Set wb = Workbooks.Open(sPath & flName)` ;) – Siddharth Rout Jan 22 '19 at 11:44
ok brill this worked! only thing is even though i removed the msg box a msg box still opened :/ think i accidently added something somewhere but may thanks! – PGD15 Jan 22 '19 at 11:45

Opening an excel file with the most recent date name in the file

1 Answers1