Solved

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

Posted on 2014-01-09
10
161 Views
Last Modified: 2014-01-14
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-
0
Comment
Question by:RWayneH
  • 5
  • 4
10 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 39769967
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
 
LVL 80

Expert Comment

by:byundt
ID: 39770032
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
 

Author Comment

by:RWayneH
ID: 39770657
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
 

Author Comment

by:RWayneH
ID: 39770662
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
 
LVL 80

Expert Comment

by:byundt
ID: 39771600
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:RWayneH
ID: 39773273
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
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39773451
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
 

Author Closing Comment

by:RWayneH
ID: 39776888
EXCELlent! and thanks.  Worked great -R-
0
 

Author Comment

by:RWayneH
ID: 39780637
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
 
LVL 80

Expert Comment

by:byundt
ID: 39780944
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now