Const dtEarliest formula help

[emoandy05]

Hey,

I have the following formula:

Const dtEarliest = "11/20/2015"

How to I set the date in the parenthesis to automatically be today's date minus 10 days?


Thank you.

Emerson

 

[BobUmlas]

As a Const, you can't.
But you can dim dtEarliest as Date and in the open routine you can set it:

Private Sub Workbook_Open()
dtEarliest = Date - 10
End Sub

and in a regular module:
Public dtEarliest As Date

 

[emoandy05]

this is my full code.... would this work?

Dim dtTestDate As Date
    Dim sStartWB As String
    Public dtEarliest As Date
    
    Const sPath As String = "Z:\Report\"
    dtEarliest = now()-10


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

 

[emoandy05]

So that does work, thanks for your help! I do get a message though until my macro finds a matching date. To dismiss it, i have to click ok, and it proceeds to the next date. Any way to remove the notification?

 

[emoandy05]

So that does work, thanks for your help! I do get a message though until my macro finds a matching date. To dismiss it, i have to click ok, and it proceeds to the next date. Any way to remove the notification?

this works: Application.DisplayAlerts = False

Thanks.