Full Version: Using wildcards in file names.
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
dflak
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?

CODE
Public Function FileDate(FileName As String, Optional PathName As String) As Date
Application.Volatile
Dim myFSO, myFileObject
Dim datLastModified As Date 'Date and time the file was last modified.

If Len(PathName) = 0 Then
    PathName = ThisWorkbook.path
End If

FileName = PathName & "\" & FileName

Set myFSO = CreateObject("Scripting.FileSystemObject")
Set myFileObject = myFSO.GetFile(FileName)

With myFileObject
    datLastModified = .dateLastModified
End With

FileDate = datLastModified

End Function
doctor9
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.

CODE
  
Sub GetAListOfFileDates()

    Dim MyFile As String, strPath As String, strFilename As String
    Dim dteFileDate As Date

'   Cycle through a series of similar filenames
    strPath = "\\server\path\March"
    strFilename = "foo*.xls" 'Sorry, no question marks for wildcards.
    
    MyFile = Dir(strPath & strFilename)
    While MyFile <> ""
        
        dteFileDate = FileDate(MyFile, strPath)
'       Do whatever it is you do with the file date
        
        MyFile = Dir
    Wend
    
End Sub

Hope this helps,

Dennis
dflak
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.
doctor9
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
dflak
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.
doctor9
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
dflak
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.