Go get a Text file, but only if it has todays date on it.

Yes it's totally possible to check on modified date. Will you finish the script, or does it have to be finished by an Expert ? If it has to be finished by an Expert, your description is not complete. How does it work ? How does it now where to search ? Will you point the folder ? Is it a fixed folder? Will it loop by itself through all text files ? Will it stop after one hit ? Tell exactly what you want, step by step.

You can use the FileSystemObject to access the DateLastModified property of a file. This will return the date & time of the last modification. If it is less than the value returned by the Date function, then the file is from a previous day.

As written, the macro displays a file browser to let the user pick a file. The macro then checks the date it was last modified and displays an error message if it was before today.

Sub TodaysTxt()
Dim fl As Object, fso As Object
Dim fileSpec As String      'Will be populated with path & filename of chosen txt file
fileSpec = Application.GetOpenFilename("Text files (*.txt), *.txt", Title:="Please pick the file of interest, then click 'Open'")
If fileSpec = "False" Then Exit Sub

Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
If CDate(fl.DateLastModified) < Date Then
    MsgBox "The chosen txt file was modified before today. Please find a more recent file."
    Exit Sub
End If

'Your code goes here
End Sub

Select allOpen in new window

Ok sounds like this is possible.  We want to use this to alert the user that the files they are about to use have not been updated with the current days data, and.....  sorry Exit Sub, with message that you may not want to use yesterdays datasets to produce todays reports.  Here is a sample of the code I use to pull a file.  Is it possible to check this before the old sheet tab is deleted?
Please advise and thanks. -R-

'Delete old sheet tab
    Application.DisplayAlerts = False
    Sheets("CDPSRECRPT").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = False
'
'Open new days dataset from extract.
    Workbooks.OpenText Filename:= _
        "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
    

Select allOpen in new window

User will not be selecting the file, I have excel going to get the file, however I will need to use the same procedure on a number of files that I draw into one workbook.. if file will exist in the folder from the previous day.. it just needs to check it, if not todays date.. Exit.  Hope these details help explain.  -R-

I put your code in the Else section of a big If block. That should help you apply the same procedure to a number of files.

Sub TodaysFiles()
Dim fl As Object, fso As Object
Dim fileSpec As String

'Check if file has been updated
fileSpec = "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
If CDate(fl.DateLastModified) < Date Then
    MsgBox "The file you are about to use has not been updated with today's data." & vbLf & _
        "You probably don't want to use yesterday's dataset to produce today's report"
    Exit Sub
Else

    'Delete old sheet tab
        Application.DisplayAlerts = False
        Sheets("CDPSRECRPT").Delete
        Application.DisplayAlerts = False
    '
    'Open new days dataset from extract.
        Workbooks.OpenText Filename:= _
            "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="|", TrailingMinusNumbers:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
                Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), _
                Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
                Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1))
     
     'Do more stuff with this file if you like
End If
End Sub

Select allOpen in new window

This is working, sort of an off question in the MsgBox.  what is: & vbLf & _  and how would I place an additional space between sentences?  I think it is starting a new line,  but I would like to add another gap between the two statements.  -R-

EXCELlent! and thanks.  Worked great -R-

Sorry not sure if it too late to ask, but is there a way to check the file to see if it is a couple hrs old?  That will mean that the procedure is using file that have not been recently extracted.  It works great the way it is written for the morning rpts but when the evening reports are run it can work using the morning extracts instead of the evening ones..  Any chance we can update this to say if the file is 3hrs go.. don't use it either even through today's date is on it... if the date on the file is more than 3 hours old, we are using a bad file.  -R-

I changed from an If test to a Select Case so I could test for more than 12 hours difference (probably yesterday's data) or more than 3 hours difference (this morning's data). The error messages are different for the two possibilities.

Sub TodaysFiles()
Dim fl As Object, fso As Object
Dim fileSpec As String

'Check if file has been updated
fileSpec = "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fl = fso.GetFile(fileSpec)
Select Case (Now - CDate(fl.DateLastModified))
Case Is > TimeSerial(12, 0, 0)
    MsgBox "The file you are about to use has not been updated with today's data." & vbLf & vbLf & _
        "You probably don't want to use yesterday's dataset to produce today's report"
    Exit Sub
Case Is > TimeSerial(3, 0, 0)
    MsgBox "The file you are about to use has not been updated with the latest data." & vbLf & vbLf & _
        "You probably don't want to use this morning's dataset to produce the afternoon report"
    Exit Sub
Case Else

    'Delete old sheet tab
        Application.DisplayAlerts = False
        Sheets("CDPSRECRPT").Delete
        Application.DisplayAlerts = False
    '
    'Open new days dataset from extract.
        Workbooks.OpenText Filename:= _
            "C:\_SAP\ShortageRpt\DefaultExtractFiles\CDPSRECRPT.TXT", Origin:=437, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=True, OtherChar:="|", TrailingMinusNumbers:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
                Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), _
                Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
                Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1))
     
     'Do more stuff with this file if you like
End Select
End Sub

Select allOpen in new window