date validation in a text file using vba macros

Question

i have a flat file (text file) having continuous data. i need to write a macro to validate the date in this text (from offset 7 to 15). The Is Date() function requires the input to be in year/mm/dd format, but all i have is a flat file like above mentioned. is there any solution for this in batch scripting or excel macro. please help.

The text file looks like this:

aaaaa 20130202 rt bbbbb 20080210 lt cccccc 20150815 gf

Please can you post an example of the text in the file and what you want to identify as a correct or incorrect date? Also what do you want to do with this file, just check it for errors or load it into Excel for further analysis? In the latter case a worksheet function might do the job. — nekomatic, Feb 12 '15 at 11:01
i need to validate the date fields present...that if it contains a valid date (including the leap year validation). I dont want to load it to excel, i just want to know if all the dates in this file are valid. — shruthi, Feb 12 '15 at 11:18
The problem i see is, my data is continuos like- 20080210, but not formatted like 2008/02/10. how to overcome this? Thanks in advance :-) — shruthi, Feb 12 '15 at 11:20

score 0 · Answer 1 · answered Feb 12 '15 at 12:08

0

Convert toYYYY-MM-DD then test:

strLine = "cccccc 20150815 gf"
strDate = Mid$(strLine, 8, 4) & "-" & Mid$(strLine, 12, 2) & "-" & Mid$(strLine, 14, 2)

If IsDate(strDate) Then ...

answered Feb 12 '15 at 12:08

Alex K.

171,639
30
264
288

The OP's example suggests that the first field isn't always the same width - might need to use `split` to get the date field in its own string then apply this answer to that. – nekomatic Feb 12 '15 at 13:05

score 0 · Accepted Answer · edited May 23 '17 at 10:24

My first thought went to Regular Expressions (RegEx) as a possible solution for this problem since extracting the dates could be otherwise problematic.

There's some great info on how to use RegEx in Excel here: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

I slapped together a quick subroutine to test a simple regular expression searching for the date format you presented. This assumes that there are no other numeric characters in the tested string. You will need to add a reference to MS VB Regular Expressions (the above link shows how). NOTE: I deliberately inserted an erroneous date "20121313" to test functionality.

Sub doDates()

    Dim strInput As String
    Dim strPattern As String
    Dim strDate As String
    Dim regEx As New RegExp

    strInput = "aaaaa 20121313 rt bbbbb 20080210 lt cccccc 20150815 gf"

    strPattern = "([0-9]){4}([0-9]){2}([0-9]){2}"

    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    Set collResult = regEx.Execute(strInput)

    For Each testDate In collResult
        strDate = Mid(testDate, 5, 2) & "/" & Right(testDate, 2) & "/" & Left(testDate, 4)
        If Not IsDate(strDate) Then
            MsgBox ("Bad date found: """ & strDate & """")
            Exit Sub
        End If
    Next

    MsgBox ("All dates test ok")

End Sub

oh, and strPattern = "([0-9]){8}" works just as well – Porcupine911 Feb 12 '15 at 23:11 — Porcupine911, Feb 12 '15 at 23:11

date validation in a text file using vba macros

2 Answers2