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

Open File with name incorporating ~yesterday's date
 

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" (notmorespam) wrote in message
...
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

Open File with name incorporating ~yesterday's date
 

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" (notmorespam) wrote in message
...
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