I am using this code:

Dim s1 As String, s2 As String, sName As String
s1 = "C:\EPLC\Audits"
s2 = "C:\EPLC\Audits\Completed\"
sName = Format(Date, "ddmmyy") & " NW AB.xls"
FileCopy s1 & sName, s2 & sName
Workbooks.Open s1 & sName
Kill s1 & sName

but what i want to do is if the day is Monday i want to load an .xls
file with filename :
"date of the friday previous + NW AB.xls"

so this week for instance.
as today is Wednesday i want to load the .xls file with the filename :

"30.07.2004 NW AB.xls"

Can anyone please suggest the code to fulfill this need.

Many thanks


---
Message posted from http://www.ExcelForum.com/

DOH!

What am i on ??

I mean as today is Monday then i want to load last fridays fil

--
Message posted from http://www.ExcelForum.com

If not Monday, it loads the today date file?

Dim s1 As String, s2 As String, sName As String
s1 = "C:\EPLC\Audits"
s2 = "C:\EPLC\Audits\Completed\"
If Weekday(Date) = 2 Then
sName = Format(Date-3, "ddmmyy") & " NW AB.xls"
Else
sName = Format(Date, "ddmmyy") & " NW AB.xls"
End If
FileCopy s1 & sName, s2 & sName
Workbooks.Open s1 & sName
Kill s1 & sName


--
HTH

-------

Bob Phillips
"Jako " wrote in message
...
I am using this code:

Dim s1 As String, s2 As String, sName As String
s1 = "C:\EPLC\Audits"
s2 = "C:\EPLC\Audits\Completed\"
sName = Format(Date, "ddmmyy") & " NW AB.xls"
FileCopy s1 & sName, s2 & sName
Workbooks.Open s1 & sName
Kill s1 & sName

but what i want to do is if the day is Monday i want to load an .xls
file with filename :
"date of the friday previous + NW AB.xls"

so this week for instance.
as today is Wednesday i want to load the .xls file with the filename :

"30.07.2004 NW AB.xls"

Can anyone please suggest the code to fulfill this need.

Many thanks


---
Message posted from http://www.ExcelForum.com/