Macro for Text to column

[xtreemnet]

Hi,

I have a fixed width text file which I am trying to import into Excel sheet. When I use Text-To-columns to import the dates are imported correclty(dd-mm-yyyy) as in the text file.
However if I record a macro for this operation and then try to import the file, the dates are not imported correctly.
For example a date 06/01/2006 is imported as 01/06/2006.
I have tried to using the option "DMY" while using theText-to-column wizard. But there is no change.
The regional setting for my machine says date as "dd-mm-yyyy".

Can somebody tell me what's going wrong?

Thanks,

 

[RonRepp]

try adding this to the end of your macro:

    Columns("A:A").Select 'Whatever column it is in
    Selection.NumberFormat = "dd-mm-yyyy"

I hope this helps

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[PHV]

Have a look at the FieldInfo named parameter of the TextToColumns method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886