Open Latest TextFile with latest TimeStamp

**pr4t3ek** · 03-03-2009, 07:16 AM

Hi,

An application saves a text file in a local folder with a specific textfile name
The text in the textfiles are exactly the same, they just vary in figures.

e.g. of textfile names in the folder & timestamps
RS00A1012009 17/02/2009 5:50PM
RS00B1012009 17/02/2009 7:09PM
RS00C1012009 17/02/2009 7:15PM
RS00A1001009 18/02/2009 5:00AM
RS00B1001009 18/02/2009 6:00AM

Now what i would like a macro to do is import the "latest text file" from the above list which has the text "A" in it... so therefore the textfile i would like to open in the above instance is:
RS00A1001009 18/02/2009 5:00AM

I would therefore then like it to be opened in the following code fashion

    Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\L487698\Desktop\THELATESTFILE.txt", Origin:=-535, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(19 _
        , 1), Array(29, 1)), TrailingMinusNumbers:=True

Where the "THELATESTFILE" is the file i want to open as suggested above...

hope someone can help, thanks

**Charlize** · 03-03-2009, 10:03 AM

Not tested but maybe something like this :

Sub check_on_dir()
'RS00A1012009 17/02/2009 5:50PM
'RS00B1012009 17/02/2009 7:09PM
'RS00C1012009 17/02/2009 7:15PM
'RS00A1001009 18/02/2009 5:00AM
'RS00B1001009 18/02/2009 6:00AM
Const mydir As String = "C:\Data\"
Dim myfile As String
Dim mydate As Date
Dim mystrdate
Dim resultfile As String

mydate = DateSerial(1990, 1, 1)
myfile = Dir(mydir & "*.txt")
Do While myfile <> vbNullString
    If Left(myfile, 5) = "A" Then
        mystrdate = Split(Split(myfile, " ")(1), "/")
        If DateSerial(mystrdate(2), mystrdate(1), mystrdate(0)) > mydate Then
            mydate = DateSerial(mystrdate(2), mystrdate(1), mystrdate(0))
            resultfile = myfile
        End If
    End If
    myfile = Dir
Loop
Workbooks.OpenText Filename:= _
    "C:\Documents and Settings\L487698\Desktop\" & resultfile, Origin:=-535, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(19 _
    , 1), Array(29, 1)), TrailingMinusNumbers:=True
End Sub

Charlize