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.
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!
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!