Somebody gave me the very nice function at the end of this posting to determine the date stamp on a file. It works great.

My boss likes it so much that he wants me to use it as a tool to see if people are submitting their reports on time. (There's only about 150 of these). The problem is, these reports have date stamps and the name changes each time the report is issued. It's not so much of an issue with most of the daily reports as thier date stamps are predictable and I can generate a file name from a root name based on the current day.

However, the weekly and monthly reports can be issued on any number of days. For example, the "foo report" is due within the first 5 days of the month, so it could be foo080301.xls, foo080302.xls ,... foo080305.xls. All I know is that the format will be YYMMDD. There are differences in the format of date stamps for different reports. Some even stamp down to the second of generation.

Is there a way I can specify the file name in such a way to accept a wildcard. For example "foo??????.xls" I know that there will be an issue if there are more than one file that matches the pattern; for example foo080201 and foo080303. I am willing to live with finding the last (preferably) or first (if I have to) of these files. I wil handle the ambiguity procedurally. (I.e. you have to put the report in the folder of the month.). Also I could get smart enough to use month and year to build the string so it looks like foo0803??.xls before feeding it to the function.

So I would like to call the following function with arguments like so: FileDate("foo??????.xls","\\server\path\March")

If it helps, the date stamp is always at the end of the file name before the extention. E.g foo080303.xls or foo080303.pdf or foo080303.csv.

Can do?

Dan,

Rather than modify a perfectly nice function, do your looping through the list of files OUTSIDE of the function, and call it once for each file in the list.


Hope this helps,

Dennis

Thanks for getting me off to a good start. I will experiment with this.

I already have one problem I can see. I have two reports: "Foo_datestamp.xls" and "Foo_lite_datestamp.xls." There are a couple of other reports with names like this as well. Luckily, it's a handful. I don't know if I can convince the user into changing names.

However, your approach offers one really nice feature. In the case of ambiguity, I can take the maxium of the dates returned - most currently submitted report.

You can put an IF test inside the loop that allows you to skip over checking the date of a few exceptions.

While MyFile <> ""
If instr(1,MyFile,"stamp")=0 Then

Dennis

Here is the "finished product" It returns the latest date stamp of any file that matches the pattern which is pretty much what I want. [code]
Function GetFileDates(FileName As String, Optional FilePath As String) As Date
Dim MyFile As String
Dim dteFileDate As Date
Dim MaxDate As Date
' Cycle through a series of similar filenames
If Len(FilePath) = 0 Then
FilePath = ThisWorkbook.Path
End If

MaxDate = 0

MyFile = Dir(FilePath & "\" & FileName)

While MyFile <> ""
' MsgBox MyFile
dteFileDate = FileDate(MyFile, FilePath)
If dteFileDate > MaxDate Then
MaxDate = dteFileDate
End If

MyFile = Dir
Wend
GetFileDates = MaxDate
End Function
[code]

[Edit] BTW: DOES TOO take "?" as a wildcard! At least in 2003.

Edited by: dflak on Tue Mar 4 8:40:49 EST 2008.

Glad you got it working.

Hmmm, I must've had some sort of typo when I was testing the question mark. Doubly glad that you got that working as well!

Dennis

It could be that I am passing the question mark as an argument. When embedded directly in the code as a string, it might have to be escaped.

Anyway -- the looping concept and wilcard can be used for applications well beyond just finding dates! I'm glad you "talked me into" wrapping my original code in a bigger application. It makes the application a lot more modular. I can replace the date lookup function with just about anything.