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

New Member
Joined
Jul 20, 2019
Messages
1
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,795
This should return the name of the most recent file:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,163
Messages
5,442,772
Members
405,195
Latest member
Mike Neal

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top