Open File with name incorporating ~yesterday's date

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

--
achidsey

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
"achidsey" <[email protected](notmorespam)> wrote in message
news:[email protected]...
> 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
>
> --
> achidsey

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
'===================>>


---
Regards,
Norman



"achidsey" <[email protected](notmorespam)> wrote in message
news:[email protected]...
> 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
>
> --
> achidsey