Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date looping
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date looping
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) "Jason Hancock" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date looping
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 -- Regards, Tom Ogilvy "Jason Hancock" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
looping ... date formula ... | Excel Programming | |||
Looping | Excel Programming | |||
Looping | Excel Programming | |||
Need Looping Help | Excel Programming |