Date looping

[Jason Hancock]

I have gotten my macro working thanks to all of you. Now I'm trying to
make it less interactive for the user (i.e. remove as much human error
as possible in the hands of a not so computer literate person).

To that end, is it possible to create a loop that searches for a file
named “Projections Marc {Date}”, where date is the date that the file
was saved? For example:

Filename = “Projections Marc_2004-06-03.xls”

The search starts with the basic filename of “Projections Marc_” and
runs a loop that begins with the current system date and tries to find
the file. If it fails it removes one day from the date and searches
again. It continues in this manner until it either finds the file, or
it hits the date of the Monday of that week.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Jason,

Here is a shot

testDate = Date
startDate = DateSerial(2004, 1, 1) 'set a limit

Do
If Dir("C:\myTest\Projections Marc_" & Format(testDate,
"yyyy-mm-dd")) <> _
"" Then
fFound = True
Else
testDate = testDate - 1
End If
Loop Until fFound Or testDate < startDate


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I assume you want to check the Monday date and then stop.
Dim sName as String, sPath as STring, i as long
Dim bFound as Boolean
sPath = "C:\My Folder\My Files\"
i = 0
bFound = False
Do while Weekday(date - i,vbSunday) > 1
sname = sPath & "Projections Marc_" & format(date - i, _
"yyyy-mm-dd" & ".xls"
if dir(sname) <> "" then
bFound = True
exit loop
end if
i = i + 1
Loop
if bFound then
workbooks.Open sName
else
msgbox "Not found"
End if