Importing Date Fields from Text Files

Hi

I have an issue reading in dates from a text file. The text files are output from a GIS program and if the record IS a date then it is out put in YYYYMMDD format e.g. 20070828. This is fine and my code (which I will attach later) handles this circumstance fine. However things go awry when MapInfo outputs a date that is actually a string e.g "27/08/2008". My code tries to interpret this as a date but ends up reverting to MDY and it is uncertain the result I will get in Excel.

Basically I need a way to parse the text file first to check if the record is a string or a date. Any one have any suggestions?

Below is the Code to open the text file and as you can see it is expecting a date in YMD format.

Workbooks.OpenText FileName:=vrtSelectedItem _
                     , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
                      :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
                       False, Comma:=False, Space:=False, Other:=False, 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, 5), Array(12, 5), Array(13, 5), Array(14, 1), _
                       Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 5)), TrailingMinusNumbers:=True

Cheers
Clive

Hi

you could try something like this to make a copy of the file and then change all dates in dd/mm/yyyy format to yyyymmdd

Sub fixdate()

Const ForReading = 1

Dim myStr As String
Dim newStr As String
Dim mysourcefile As String
Dim myfilecopy As String

mysourcefile = "C:\datetest.txt"
mycopyfile = "C:\datetest4.text"


Set myFSO = CreateObject("Scripting.FileSystemobject")
Set myfile = myFSO.OpenTextFile(mysourcefile, ForReading)

myStr = myfile.ReadAll()
myfile.Close

'Create a copy of the File
Set myfile = myFSO.CreateTextFile(mycopyfile)

'Use Regex to find dd/mm/yyyy pattern
Set myRegex = CreateObject("VBScript.RegExp")

With myRegex
    .Global = True
    .Pattern = "\d{2}\/\d{2}\/\d{4}"
End With

Set mymatches = myRegex.Execute(myStr)

    If mymatches.Count > 0 Then

        'Replace dd/mm/yyyy with yyyymmdd
        For Each myMatch In mymatches
       
            OldValue = myMatch.Value
       
            MyYear = Right(myMatch.Value, 4)
            MyMonth = Mid(myMatch.Value, 4, 2)
            MyDay = Left(myMatch.Value, 2)
       
            NewValue = MyYear & MyMonth & MyDay
       
            newStr = Replace(myStr, OldValue, NewValue)
            myStr = newStr
       
        Next
      
    End If

myfile.WriteLine myStr
myfile.Close

Set myRegex = Nothing
Set mymatches = Nothing


End Sub

qff