Open File with name incorporating ~yesterday's date

[Guest]

Excel Experts,

Each day, I want to open a file with path and name in the format of

T:\Statements\MMM\MCNC MMDDYY
where MMM=Current month and MMDDYY is the last business day

For example the file I opened this morning was:

T\Statements\SEP\MCNC 092005

For reference, I've used the following code to save a file with today's date
ActiveWorkbook.SaveAs Filename:= _
"T\Statements\MyFile" & Format(Date, "yymmdd") & ".xls"

Also, I've used the following code to put the date of the last business date
in a cell:
ActiveCell.FormulaR1C1 = "=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)"

So essentially what I need to do is to replace the code
Format(Date,"yymmdd") , which indicates today's date
with code that will indicate the last business day

I tried using the code I used for the formula into a FileOpen statement but
it didn't work.

Can this be done?

Thanks,
Alan

 

[Mike Fogleman]

Try this:

Sub OpenFile()
Dim Tday As Date
Dim Tmonth As String
Dim Yday As String

Tday = Date - 1
Yday = Format(Tday, "mm/dd/yy")
Tmonth = Format(Format(Tday, "mmm"), ">")
Yday = Replace(Yday, "/", "")
Workbooks.Open Filename:="T:\Statements\" & Tmonth & "\MCNC " & Yday &
".xls"
End Sub

Mike F

 

[Norman Jones]

Hi Alan,

Try:

'===================>>
Sub aTester3()
Dim sStr As String
Const strPrefix As String = "MCNC "
Const strPath As String = "T:\Statements\MMM\"

sStr = Evaluate("=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)")
sStr = Format(sStr, "yymmdd")

Workbooks.Open (strPath & strPrefix & sStr)

End Sub
'===================>>