Set Const as a dynamic/variable date

emoandy05

Board Regular
Joined
Sep 4, 2013
Messages
60
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.

Code:
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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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...

Code:
 Const dtEarliest = #11/20/2015#

To...
Code:
 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...

Code:
  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.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,829
Members
452,673
Latest member
LaMiaAvy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top