Use of 'Text to Columns' within VBA

[mralmackay]

As a result of a problem that I've got analysing data within a pivot-
table I need to remove the time stamp on the end of data that I've
imported from a flat file (csv).

Column B contains data as per below example (Title row 1 containing
Created Date):
Created Date
02/01/2007 08:10
02/01/2007 13:58
02/03/2007 10:58
02/03/2007 11:11
02/01/2007 08:41
06/03/2007 13:46
18/01/2007 15:35

I've managed to record the functionality to do what I want, however
when I try and use this back it causes dates to reverse (e.g. my
previous August dates later on in the data have instead of being
03/08/2007 have become 08/03/2007 - american format). I've tried
ensuring the format of this data was correct pre-macro and then
afterwards but to no avail.

Please can someone help. Below is the recorded code:

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.texttocolumns Destination:=Range("B2"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), Array(10, 9))

Regards, Al.

 

[Michael]

Change the Array to
FieldInfo:=Array(Array(0, 4), Array(10, 1)).

Michael Arch.

Please rate this posting if helpful

 

[Michael]

Also you can add this:
Selection.NumberFormat = "dd/mm/yyyy"

 

[mralmackay]

Hi Michael,

Thanks for this. Only prob' is that this seems to want to use the
separated data as the next column now (e.g. the time). Is it possible
to prevent it from this part? Otherwise this is fine, thank you.

Could you also possibly explain the array part as well from a logic
point of view so I understand for future ref?

Thanks again, Al.

 

[Michael]

When you are recording the macro, and do text to columns and select fixed
width, on the next step you will see a formatting section on the right hand
side of the window box that reads Column Data Format, select Date and then on
the dropdown box select DMY. You may still need to add the following
statement:
Selection.NumberFormat = "dd/mm/yyyy"
To make sure it will not change your data formatting.

Regards,

Michael Arch.