Excel Function, Search for Max wildcard (*)

Question

I have the following function that searches for a file paths, modified file date. However if my file has a wildcard at the end then this function returns a 12:00. This is because the file with the wildcard will have many options in the path and it is not sure what value to return.

How can I tell this function to grab the max wildcard file path name.

Ex: *Stack/Over/Flow_*.csv* (a1) will return 12:00(a2) because the actual folder will contain *Stack/Over/Flow_1.csv*, *Stack/Over/Flow_2.csv*, *Stack/Over/Flow_3.csv.* . The work-around I have is just changing the file card path to a 1 , and it will return a proper value. But I would like the max of this wildcard.

Is it possible to return the max of whichever modified date is greater of each of these? Or do you recommend just changing the wildcard (*) to a 1 and using function as it is?

 Public Function getmodifieddateoffile(FilePath As String)

    On Error GoTo ExitWithError

    If FilePath = "" Then
        Exit Function
    End If
    If Dir(FilePath) <> "" Then
    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    getmodifieddateoffile = oFS.GetFile(FilePath).DateLastModified
    Else
    End If

    Exit Function
    ExitWithError:

    End Function

Any help would be greatly appreciated.

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

You can loop through all files in your folder and calculate the maximum yourself ?

Method for loop :

Loop through files in a folder using VBA?

edited May 23 '17 at 10:28

Community

1
1

answered May 18 '16 at 14:53

JackIsJack

68
6

Thanks Jack, I would like to add the loop through the folder with the within my function. Some of these don't need to be looped, just the wildcard values need to be. – Jonnyboi May 18 '16 at 14:56

score 0 · Accepted Answer · answered May 18 '16 at 16:50

Following code will return the max time when using wildcards in the path

Public Function GetFileDate(strFile As String) As Date


        Dim lastDate As Date
        Dim FileName As String
        Dim FilePath As String

        FileName = Split(strFile, "\")(UBound(Split(strFile, "\")))
        FilePath = Replace(strFile, FileName, vbNullString)

        FileName = Dir(FilePath & FileName)

        Do While FileName <> vbNullString

            If lastDate < FileDateTime(FilePath & FileName) Then
                lastDate = FileDateTime(FilePath & FileName)
            End If

            FileName = Dir
        Loop

        GetFileDate = lastDate

    End Function

Excel Function, Search for Max wildcard (*)

2 Answers2