Set Const as a dynamic/variable date

[emoandy05]

Hello,

I have the following code that works well. It searched for a file saved with the most recent date in the file name and opens it.

Sub Test()
 
'---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 = "Z:Test\Report\"
    Const dtEarliest = #11/20/2015#
'--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 & Format(dtTestDate, "MM.DD.YYYY") & " Report.xlsx"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
    
    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

But, has 2 problems.

1. Until it finds the file name contianining the most recent date, it notifies me with a message box saying so as it works backwards through each date. I would like for it to not notify me that it has not found a matching file until it hit the "Const" date (I dont want to macro to look at any dates prior to the "Const" date.

2. I would like to set the "Const" to current date, minus 10 days. Currently it is hard coded as a specific date.

Thanks for you help!

 

[Jerry Sullivan]

Hi Emerson,

I saw the post you added to the original thread, and wanted to respond here since you included the code you are trying.
Original Thread: http://www.mrexcel.com/forum/excel-...d-latest-date-found-filename.html#post4357947

Starting with the easier question #2, just change this statement...

 Const dtEarliest = #11/20/2015#

To...

 Dim dtEarliest as Date
 dtEarliest = Date-10

Regarding problem #1, are you saying that the Msgbox pops up repeatedly as it tries the filename for each day? That shouldn't happen with the code that you've posted in your OP.
It could happen if the Msgbox statement was moved up into the Loop like this...

  While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & Format(dtTestDate, "MM.DD.YYYY") & " Report.xlsx"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0

  '******** don't put this statement inside the While....Wend loop. 
        If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
    Wend

If that wasn't the cause, did you have any additional code at the bottom of that procedure besides a missing "End Sub"?


Stepping back to the big picture, the approach of trying to open a file with each Date pattern until a match is found is inefficient and I wouldn't recommend it.
In that original thread, I was responding to the OP's question about how to modify some code they already had. The code shown in Post #13 that uses DIR() to test for file names would be a better example to adapt.