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

I use Excel to go get text files.  I would like to put a MsgBox up and Exit Sub if the file does not have a date of today.  File name needs to stay static.  Can we used the Date Modified as a check?  If the Date Modified does not equal today, exit and kill procedure.  -R-
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
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.
0
byundtMechanical EngineerCommented:
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

Open in new window

0
RWayneHAuthor Commented:
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
    

Open in new window

0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

RWayneHAuthor Commented:
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-
0
byundtMechanical EngineerCommented:
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

Open in new window

0
RWayneHAuthor Commented:
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-
0
byundtMechanical EngineerCommented:
The underscore is not part of the statement, but rather a continuation character making the next line part of this one. To work correctly, the underscore must both be at the end of the line and have at least one space before it.

vbLf is a line-feed character. The ampersand & is a concatenation character. So if you want abc on one line, a blank line, and then def, you would do:
MsgBox "abc" & vbLf & vbLf & "def"

Open in new window

Making that change to the previous code, you would have:
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 & 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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
EXCELlent! and thanks.  Worked great -R-
0
RWayneHAuthor Commented:
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-
0
byundtMechanical EngineerCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.