I am opening a csv file in excel with many columns in the US date
format (mm-dd-yyyy). I need these all to be in the UK format
(dd-mm-yyyy). When I run text to columns on each column individually I
can choose the column data format MDY and it will reformat the dates
correctly. However when I recorded a macro to do this it doesn't work.

Does anyone have any idea why this works when you do it manually but
not via a macro.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
FieldInfo:=Array(1, 3)

and repeated for each column

Thanks,
Andy

If possible don't use the '-' symbol for delimiting and run:
Range("A:A").NumberFormat = "dd-mm-yyyy"

Hope this helps!

- Bas

Andy wrote:
I am opening a csv file in excel with many columns in the US date
format (mm-dd-yyyy). I need these all to be in the UK format
(dd-mm-yyyy). When I run text to columns on each column individually I
can choose the column data format MDY and it will reformat the dates
correctly. However when I recorded a macro to do this it doesn't work.

Does anyone have any idea why this works when you do it manually but
not via a macro.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
FieldInfo:=Array(1, 3)

and repeated for each column

Thanks,
Andy

If the values were brought in as dates, then I think it's too late.

Excel will convert anything it sees as a date to a date--when working with .csv
files.

And the ones that don't look like dates will be brought in as text.

I think the safest thing to do is to rename the .csv file to .txt

Then record that macro when you:
File|Open your *.txt file
use the text import wizard to specify the field type
(Use the mdy format)

Andy wrote:

I am opening a csv file in excel with many columns in the US date
format (mm-dd-yyyy). I need these all to be in the UK format
(dd-mm-yyyy). When I run text to columns on each column individually I
can choose the column data format MDY and it will reformat the dates
correctly. However when I recorded a macro to do this it doesn't work.

Does anyone have any idea why this works when you do it manually but
not via a macro.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
FieldInfo:=Array(1, 3)

and repeated for each column

Thanks,
Andy

--

Dave Peterson

Thanks Dave that seems to work. Still seems strange that it works when
I run it myself in Excel but not from a macro!

I meant to add that VBA is USA centric. It sees everything as good old USA'ian.

And if you run your macro against the .csv file (instead of .txt), you'll notice
that VBA ignores your settings when it imports each field.



Andy wrote:

Thanks Dave that seems to work. Still seems strange that it works when
I run it myself in Excel but not from a macro!

--

Dave Peterson