VBA formula that automatically changes the date for a filename when using a vlookup

Question

A new workbook is created each day (sometimes multiple times a day) with a name for example, "Inventory 09-01-2019 0330.xlsm. The date and time (0330 for 3:30) changes every time the file is created. I have found VBA to open the most recently created file (http://www.xl-central.com/open-the-latest-file-in-a-folder.html) but I am having a hard time finding any code that will do that same type of search for the most recent name in a VBA vlookup. There is a good suggestion her for how to use indirect in a normal vlookup (Excel formula that automatically changes the date for a filename when using a vlookup) however I just do not have the knowledge or ability to get this into VBA. Still learning VBA at the moment and this is just too complicated to me.

I want to be able to run the macro, have it open the most recent file in a folder, then activate the vlookup that finds that opened/named file, to pull in data from the same sheet/column into the new workbook being created. Example: New workbook sheet "Agents" contains a blank column for "Comments". I want to vlookup the "Comments" from the "Agents" sheet, in the most recently created workbook in the folder. I would include examples of what I have tried so far but I fear they are so far off, its just not worth it. Thank you.

The macro that opens the file can easily get the filename, then insert VLOOKUPs using said filename as required. — cybernetic.nomad, Sep 11 '19 at 20:37
Which was a thought that I had, but I had no success inputting it into the vlookup formula. — J I, Sep 11 '19 at 20:40
Then please edit your question to include the code you have so we can see where the problem is — cybernetic.nomad, Sep 11 '19 at 20:41

score 0 · Answer 1 · answered Sep 11 '19 at 21:42

I found here a code done by Coder375 that returns an array of available names in a given folder. It goes like this:

Function listfiles(ByVal sPath As String)

    Dim vaArray     As Variant
    Dim i           As Integer
    Dim oFile       As Object
    Dim oFSO        As Object
    Dim oFolder     As Object
    Dim oFiles      As Object

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    Set oFiles = oFolder.Files

    If oFiles.Count = 0 Then Exit Function

    ReDim vaArray(1 To oFiles.Count)
    i = 1
    For Each oFile In oFiles
        vaArray(i) = oFile.Name
        i = i + 1
    Next

    listfiles = vaArray

End Function

With that, it's fairly simple to achieve what you need. Just iterate through each entry in the array, and evaluate the name, according to the structure you've brought up. I came up with the below:

Function to determine the last date from array:

Function LastEntry(ByVal myArray As Variant)
Dim element As Variant
Dim reference, timeReference As String
Dim myDate, mostRecentDate, myTime, mostRecentTime As Date

For Each element In myArray
    reference = CStr(element)

    myDate = CDate(Mid(reference, 11, 10))

    If mostRecentDate Is Null Then mostRecentDate = myDate
    Else
        If DateDiff("d", mostRecentDate, myDate) < 0 Then
            mostRecentDate = myDate

            timeReference = ConvertTime(Mid(reference, 22, 4))
            myTime = CDate(timeReference)
            If mostRecentTime Is Null Then mostRecentTime = myTime
            Else
                If DateDiff("h", mostRecentTime, myTime) < 0 Then
                    mostRecentTime = myTime
                End If
            End If
        End If
    End If
Next element
LastEntry = CStr(mostRecentDate) & " " & CStr(mostRecentTime)
End Function

Function to return the corrected string from time value found on name:

Function ConvertTime(ByVal myString As String)

Dim hour, amPm, concatenated As String

hour = Left(myString, 2)

Select Case hour
    Case "00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "24"
        amPm = "AM"
        concatenated = hour
    Case "12"
        amPm = "PM"
        concatenated = hour
    Case "13"
        amPm = "PM"
        concatenated = "01"
    Case "14"
        amPm = "PM"
        concatenated = "02"
    Case "15"
        amPm = "PM"
        concatenated = "03"
    Case "16"
        amPm = "PM"
        concatenated = "04"
    Case "17"
        amPm = "PM"
        concatenated = "05"
    Case "18"
        amPm = "PM"
        concatenated = "06"
    Case "19"
        amPm = "PM"
        concatenated = "07"
    Case "20"
        amPm = "PM"
        concatenated = "08"
    Case "21"
        amPm = "PM"
        concatenated = "09"
    Case "22"
        amPm = "PM"
        concatenated = "10"
    Case "23"
        amPm = "PM"
        concatenated = "11"
End Select
concatenated = concatenated & ":" & Right(myString, 2) & " " & amPm
ConvertTime = concatenated
End Function

I think this can help you address the issue you have. I couldn't find time to test my second snippet, but I reckon it will work as an acceptable reference for you to build your solution.

VBA formula that automatically changes the date for a filename when using a vlookup

1 Answers1