Date variables

[JZB]

Hi guys

I am trying to compare two files of data. One from today against the same file a year ago.

The first step is to open the appropriate old file from an archive folder.

However i am having difficulty telling excel to locate the appropriate file.

The old file is saved to the following.

"G:\Daily P&L Summ\2008\Aug\0820P&L.XLS"

I have done the following

Dim PREYEAR,TODAY,MTH,

PREYEAR = Format(Date, "YYYY") - 1
MTH = Format(Date, "MMM")
TODAY = Format(Date, "MMDD")


Workbooks.Open FileName:="G:\Daily P&L Summ\" & PREYEAR & "\" & MTH & "\" & TODAY & "P&L.XLS"

This works . the only problem is if the preyear variable is not a business day.

I have tried to extend the logic but cannot define the PREYEAR day

Any ideas?

I tried this:

PYBD = PREYEAR & TODAY
PYBday = Format(PYBD, "DDD") - the problem is in this variable.

but no joy.

Just a syntax issue here guys but stalling my progress

Thanks for the help in advance

JZB

[Bob Phillips]

Try this

[vba]


PrevDate = DateSerial(Year(Date) - 1, Month(Date), Day(Date))
If Weekday(PrevDate, 2) > 5 Then PrevDate = PrevDate - 1
If Weekday(PrevDate, 2) > 5 Then PrevDate = PrevDate - 1
PREYEAR = Year(PrevDate)
MTH = Format(PrevDate, "MMM")
TODAY = Day(PrevDate)
[/vba]