Help with pulling data from an excel file in a folder to automatically update the date from inside that file to my work book

[saintsfan77]

Hi,
I have a workbook with a daily report Sheet. I (move and copy) it each night and rename the file to the "Daily Report- mm/dd/yyyy, date of that work day. That file gets stored in a job number folder on the desktop and in that folder is another folder named Daily Reports. I have another sheet in the workbook named Email Body. In that sheet I have a macro button that copies the subject for an email. the Subject is pulling the job number, well, rig from the Job info sheet and the date from the daily report.

I would like the name of the subject to change the date at the end, to the cell in the Daily Report that contains the date. I would like it to automatically pull from the last generated Daily Report in the folder if thats possible. So if I make a file called Daily Report 7-19-2019.xls, it should grab the date from the cell in that file.

Instead I have to tell my formula which file to look for every day.
The formula is =CONCATENATE('Job Info.'!D6," / ",'Job Info.'!D7," / ", 'Job Info.'!D9," / ", 'Job Info.'!D5," /", "Daily Report - ",TEXT('C:\Users\GTMWD\Desktop\GT190681\Daily Report\[Daily Report 7-18-19.xlsx]Daily'!K10,"mm/dd/yy;@"))

Any help would be appreciated. Thank you all.

 

[pbornemeier]

This should return the name of the most recent file:

Option Explicit
'https://www.mrexcel.com/forum/excel-questions/1104564-help-pulling-data-excel-file-folder-automatically-update-date-inside-file-my-work-book.html
Function GetMostRecentFile() As String
    'Return the most recent file named like: "Daily Report*.xls*"
    '  from the folder defined in Job Info. Ranges [D6], [D7], [D9], [D5]
    Dim sFilePath As String
    'Get the path of the folder to search
    With Worksheets("Job Info.")
        sFilePath = Join(Array([D6], [D7], [D9], [D5]), "\") & "\"
    End With

    Dim sFilePathNameExt As String
    Dim sFileNameExt As String
    Dim dteFileCheck As Date
    Dim dteFileMax As Date
    Dim sPattern As String
    Dim sRegExPattern As String
    Dim sDatePart As String
    Dim sNewestFileNameExt As String
    Dim varReturn As Variant

    sPattern = "Daily Report*.xls*"
    sRegExPattern = "(([0-9][0-9]|[0-9])-){2}([0-9]{4}|[0-9]{2})" 'Date any combo of 2/1 digit months/days & 4/2 digit years
    
    dteFileMax = #12/31/1900#
    sFileNameExt = Dir(sFilePath, vbNormal)
    Do While sFileNameExt <> vbNullString
        If sFileNameExt Like sPattern Then
            varReturn = RegExMatch(sFileNameExt, sRegExPattern)
            If IsArrayAllocated(varReturn) Then
                sDatePart = varReturn(0, 0)
                dteFileCheck = CDate(sDatePart)
                If dteFileCheck > dteFileMax Then
                    dteFileMax = dteFileCheck
                    sNewestFileNameExt = sFileNameExt
                End If
            End If
        End If
        sFileNameExt = Dir
    Loop
    If sNewestFileNameExt = vbNullString Then
        MsgBox 'No matching files found"
        End
    End If
    GetMostRecentFile = sNewestFileNameExt
    
End Function

Function RegExMatch(sInput As String, sPattern As String)
    'Locates a pattern of characters in an input string\
    
    Dim objRegEx As Object
    Dim mymatches As Object 'Not an array, but contents can be accessed like 0...Count-1 array
    Dim lX As Long
    Dim varOutput() As Variant
    
    Set objRegEx = CreateObject("vbscript.regexp")
    With objRegEx
        .Pattern = sPattern
        .IgnoreCase = True
        .Global = True  'Match all occurrences in string
        .MultiLine = False
    End With
    Set mymatches = objRegEx.Execute(sInput)
    
    For lX = 0 To mymatches.Count - 1
        ReDim Preserve varOutput(0 To 2, 0 To lX)
        varOutput(0, lX) = mymatches(lX)
        varOutput(1, lX) = mymatches(lX).FirstIndex 'starts at 0
        varOutput(2, lX) = mymatches(lX).Length
    Next
    
    RegExMatch = varOutput
    
    Set mymatches = Nothing
    
End Function

Function IsArrayAllocated(Arr As Variant) As Boolean
    'http://www.cpearson.com/excel/isarrayallocated.aspx
    On Error Resume Next
    IsArrayAllocated = IsArray(Arr) And _
                       Not IsError(LBound(Arr, 1)) And _
                       LBound(Arr, 1) <= UBound(Arr, 1)
End Function