[AccessD] OT: Excel date import problem

I am having a  problem importing dates from a text file into a worksheet due to the UK date system.

Every two weeks we download data from 25 banks in the Caribbean. The files come in as text files. Each 

row in the file is 773 characters long. The rows include amounts and dates.
In order to validate the file before doing a data load onto the main system we import each file into 

Excel as a fixed width file. 
The purpose is to check for date input errors and this works fine. For example, when a date column is 

expanded to 20 the result can look like this

              AA
¦          02/06/1943¦
¦          12/03/1956¦
¦07/30/1952          ¦
¦          22/11/1978¦

The date in row 3 is in US format and we can go to the text file and change it.

When the open/import of the text file is done manually this works perfectly, and whilst recording a 

macro to do it all is well. However run the macro and the result is

              AA
¦          02/06/1943¦
¦          12/03/1956¦
¦          30/07/1952¦
¦          22/11/1978¦

It has corrected the errors but we don’t know where they are so can’t change them in the text file.

This is part of the macro:


Sub ImportTextFile()

Dim Total As Long

' ImportTextFile Macro
' Macro recorded 31/01/2007 by Chris Enright
'
' Keyboard Shortcut: Ctrl+i
'

    'Enter drive and directory you want open dialogue box to default to:
    ChDrive "S:\"
    ChDir "S:\TPES\490\07\"
    myfile = Application.GetOpenFilename("All Files,*.*")
    
        If myfile = False Then
            Exit Sub
        End If
        
   
    'Import the selected fixed width file and set the columns
    Workbooks.OpenText Filename:=myfile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(10, 1), Array(25, 1), Array(75, 1), Array(125, 1), _
        Array(128, 1), Array(148, 1), Array(149, 4), Array(159, 1), Array(162, 1), _
        Array(202, 1), Array(206, 1), Array(246, 1), Array(256, 1), Array(296, 1), _
        Array(336, 1), Array(376, 1), Array(416, 1), Array(456, 1), Array(486, 1), _
        Array(526, 1), Array(566, 1), Array(606, 1), Array(646, 1), Array(686, 1), _
        Array(716, 1), Array(726, 1), Array(736, 1), Array(741, 4), Array(751, 4), _
        Array(753, 4), Array(763, 4), Array(773, 1)), TrailingMinusNumbers:=True
    
    
End Sub

This is driving me crazy!

I would appreciate any help.

Chris
 
---------------------------------
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.