JZB
08-20-2009, 05:21 AM
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.:banghead:
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
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.:banghead:
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