Problem with macro importing date.

[Jimbo Razza]

Hi There

I have recorded a macro that imports data and splits the data using the text import function set at fixed width.

One of the resulting columns of data is a date.

When I run the macro, any dates from the 1st to the 12th of any month get messed up.

I.e. 01/12/2007 becomes 12/01/2007.

It only does it on dates that range from the 1st to the 12th and all other dates come out fine.

It only does it when I run the macro. When I'm recording the macro or if I do the steps manually, all dates are fine.

 

[wageslave101]

What does the script for the macro look like mate?

WageSlave

 

[jindon]

UDF, if you like...
=jimbo(A1)

Function jimbo(txt As String) As String
With CreateObject("VBScript.RegExp")
     .Pattern = "^((1[0-2]|0[1-9]))/(\d{2})/(\d{4})$"
     jimbo = IIf(.test(txt) = True, .replace(txt,"$3/$1/$4"), txt)
End With
End Function

 

[Jimbo Razza]

' Import and paste file
Dim TooHostToday

        FileToday = InputBox("Todays File Number Please", "Summary Import", "000")

    Workbooks.OpenText Filename:= _
        "\\xxxxx\xxxxx\xxxxx\file" & FileToday & ".TXT" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(20, 1), Array(26, 1), Array(32, 1), _
        Array(38, 1), Array(45, 1), Array(53, 1), Array(59, 1), Array(66, 1)), _
        TrailingMinusNumbers:=True