CODEWITHSUNDEEP
excelvba
PGD15
PGD15

Reputation: 183

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

enter image description hereI 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

Upvotes: 1

Views: 1206

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 1

Related Questions