Opening Files Saved as Dates in VBA

[trm85]

I am working on a VBA code to open a password protected excel file. The file name is includes the days date in it, however the naming convention is not always consistent - Sometimes days or months may have a leading 0 while other times they will not. For example the file's name for today could potentially be "12-04-2015" or "12-4-2015".


Is there a code that would open the file if it is saved in either "MM-DD-YYYY" or "MM-D-YYYY" format? Maybe incorporating a wildcard?


The code I currently have is:


Workbooks.Open Filename:= _
"C:\Operations\Reports\Testing\" & Format(Date, "MM-DD-YYYY") & ".xlsx", Password:="test"


Thank you!

 

[AlphaFrog]

Hi and welcome to the forum.

Try something like this...

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color], strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    strPath = "C:\Operations\Reports\Testing\"
    strFile = Dir(strPath & Format(Date, "MM-DD-YYYY") & ".xlsx")
    [color=darkblue]If[/color] strFile = "" [color=darkblue]Then[/color] strFile = Dir(strPath & Format(Date, "MM-D-YYYY") & ".xlsx")
    [color=darkblue]If[/color] strFile = "" [color=darkblue]Then[/color] strFile = Dir(strPath & Format(Date, "M-DD-YYYY") & ".xlsx")
    [color=darkblue]If[/color] strFile = "" [color=darkblue]Then[/color] strFile = Dir(strPath & Format(Date, "M-D-YYYY") & ".xlsx")
    [color=darkblue]If[/color] strFile <> "" [color=darkblue]Then[/color]
        Workbooks.Open Filename:=strPath & strFile, Password:="test"
    [color=darkblue]Else[/color]
        MsgBox "Today's file not found. "
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]