I'm trying to use VBA (Excel 2013 & Excel 2010) to open an existing worksheet with a file name that contains a date - specifically, the date of the last business day. But I'm having trouble identifying the most efficient way to use VBA to account for weekends and holidays. I've found the WORKDAY function does not work "directly" in VBA, but I've been successful in defining cell formulas in Excel via VBA.

The following code works when opening and saving files, but does not account for weekends and holidays:

Dim CurrDate, LastDate, PrevDate, PriorDate
Dim FilePath, ExtractFile As String
CurrDate = Format(Date, "dd-mmm-yyyy")
LastDate = Format(DateAdd("d", -1, Date), "yyyy-mmm-dd")
PrevDate = Format(DateAdd("d", -2, Date), "yyyy-mmm-dd")
PriorDate = Format(DateAdd("d", -3, Date), "yyyy-mmm-dd")
FilePath = "Q:\Transfer\June\VBA\"
ExtractFile = "unrecon.csv"

' Open spreadsheet
    Workbooks.Open Filename:=FilePath & PrevDate & "_Outages.xlsx"
' Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & LastDate & "_Outages.xlsx"
    Application.DisplayAlerts = True
This code successfully creates a new sheet called "Dates", but does not allow me to open a workbook with a date in the file name.:

Dim CurrDate, LastDate, PrevDate, PriorDate As String
Dim FilePath As String

    Application.DisplayAlerts = False
   ' Sheets("Dates").Delete
    Application.DisplayAlerts = True
    Sheets.Add After:=Worksheets("sheet1")
    Sheets("Sheet2").Name = "Dates"
    Range("A3") = "TODAY"
    Range("B3").FormulaR1C1 = "=today()"
    Range("A4") = "{last}"
    Range("B4").FormulaR1C1 = "=workday(R[-1]C,-1,R9C2:R17C2)"
    Range("A5") = "{prev}"
    Range("B5").FormulaR1C1 = "=workday(R[-2]C,-2,R9C2:R17C2)"
    Range("A6") = "{prev}-1"
    Range("B6").FormulaR1C1 = "=workday(R[-3]C,-3,R9C2:R17C2)"
    Range("A8") = "Holidays"
    Range("A9") = "New Years Day"
    Range("B9") = "1/1/2014"
    Range("A10") = "MLK Day"
    Range("B10") = "1/20/2014"
    Range("A11") = "Washington's Birthday"
    Range("B11") = "2/17/2014"
    Range("A12") = "Good Friday"
    Range("B12") = "4/18/2014"
    Range("A13") = "Memorial Day"
    Range("B13") = "5/26/2014"
    Range("A14") = "Independence Day"
    Range("B14") = "7/4/2014"
    Range("A15") = "Labor Day"
    Range("B15") = "9/1/2014"
    Range("A16") = "Thanksgiving Day"
    Range("B16") = "11/27/2014"
    Range("A17") = "Christmas"
    Range("B17") = "12/25/2014"
    Columns("A:A").Font.Bold = True
    Columns("B:B").NumberFormat = "yyyy-mmm-dd"

'Identify source for dates
    Sheets("Dates").Select
    CurrDate = Format(Range("B3"), "yyyy-mmm-dd")
    LastDate = Format(Range("B4"), "yyyy-mm-dd")
    PrevDate = Format(Range("B5"), "yyyy-mm-dd")
    PriorDate = Format(Range("B6"), "yyyy-mm-dd")
    FilePath = "Q:\Transfer\June\VBA\"
    ExtractFile = "unrecon.csv"
    
' Open spreadsheet
    Workbooks.Open Filename:=FilePath & PrevDate & "_Outages.xlsx"
' Save as new file name
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath & LastDate & "_Outages.xlsx"
    Application.DisplayAlerts = True
The error I receive reads, "Sorry, we couldn't find 'Q:\Transfer\June\VBA\2014-01-06_Outages.xlsx". Is it possible it was moved, renamed or deleted?" I've confirmed the file does exist, and the original code listed above has no trouble opening the file.

Is there a tag/command/flag somewhere that I'm missing?

Thank you!