Hi,

I cant understand why this isnt working so time for a little help.

I have a query table in excel 2003 that pulls its data from a number of other query tables in the same workbook. The second table uses sql union which seems to convert all dates to text.

Now when i run text to columns manually it converts the dates correctly, however if i record this then use vba as is required i am left with mixed results. I have tried a number of different approaches to the vba code and all leave the same results as below:

Original		      Manual Text to columns		      VBA (items without trailing zeros are on right of cell)
29/11/2012 12:38		29/11/2012 12:38		29/11/2012 12:38:00
27/11/2012 05:10		27/11/2012 05:10		27/11/2012 05:10:00
29/11/2012 08:55		29/11/2012 08:55		29/11/2012 08:55:00
28/11/2012 16:26		28/11/2012 16:26		28/11/2012 16:26:00
27/11/2012 13:41		27/11/2012 13:41		27/11/2012 13:41:00
29/11/2012 07:50		29/11/2012 07:50		29/11/2012 07:50:00
28/11/2012 10:20		28/11/2012 10:20		28/11/2012 10:20:00
25/11/2012 15:48		25/11/2012 15:48		25/11/2012 15:48:00
24/11/2012 15:39		24/11/2012 15:39		24/11/2012 15:39:00
24/11/2012 08:04		24/11/2012 08:04		24/11/2012 08:04:00
26/11/2012 10:11		26/11/2012 10:11		26/11/2012 10:11:00
26/11/2012 07:14		26/11/2012 07:14		26/11/2012 07:14:00
26/11/2012 08:07		26/11/2012 08:07		26/11/2012 08:07:00
27/11/2011 10:48		27/11/2011 10:48		27/11/2011 10:48:00
27/11/2012 09:48		27/11/2012 09:48		27/11/2012 09:48:00
24/11/2012 08:42		24/11/2012 08:42		24/11/2012 08:42:00
24/11/2012 10:58		24/11/2012 10:58		24/11/2012 10:58:00
24/11/2012 12:22		24/11/2012 12:22		24/11/2012 12:22:00
22/11/2012 15:55		22/11/2012 15:55		22/11/2012 15:55:00
22/11/2012 10:05		22/11/2012 10:05		22/11/2012 10:05:00
22/11/2012 13:29		22/11/2012 13:29		22/11/2012 13:29:00
05/01/2012 14:11		05/01/2012 14:11		01/05/2012 14:11
03/10/2012 13:18		03/10/2012 13:18		10/03/2012 13:18
31/10/2012 15:29		31/10/2012 15:29		31/10/2012 15:29:00
15/11/2012 13:04		15/11/2012 13:04		15/11/2012 13:04:00
08/10/2012 10:33		08/10/2012 10:33		10/08/2012 10:33
08/11/2012 10:33		08/11/2012 10:33		11/08/2012 10:33
19/11/2012 09:36		19/11/2012 09:36		19/11/2012 09:36:00
21/11/2012 14:13		21/11/2012 14:13		21/11/2012 14:13:00
22/11/2012 13:40		22/11/2012 13:40		22/11/2012 13:40:00
21/11/2012 15:46		21/11/2012 15:46		21/11/2012 15:46:00
20/11/2012 13:56		20/11/2012 13:56		20/11/2012 13:56:00
05/11/2012 09:09		05/11/2012 09:09		11/05/2012 09:09
26/09/2012 09:16		26/09/2012 09:16		26/09/2012 09:16:00
17/11/2012 17:52		17/11/2012 17:52		17/11/2012 17:52:00
22/11/2012 09:22		22/11/2012 09:22		22/11/2012 09:22:00
20/11/2012 14:07		20/11/2012 14:07		20/11/2012 14:07:00
21/11/2012 06:51		21/11/2012 06:51		21/11/2012 06:51:00
21/11/2012 13:36		21/11/2012 13:36		21/11/2012 13:36:00
13/11/2012 15:22		13/11/2012 15:22		13/11/2012 15:22:00
07/11/2012 13:59		07/11/2012 13:59		11/07/2012 13:59
18/11/2012 06:40		18/11/2012 06:40		18/11/2012 06:40:00
21/11/2012 09:58		21/11/2012 09:58		21/11/2012 09:58:00
21/11/2012 12:53		21/11/2012 12:53		21/11/2012 12:53:00
21/11/2012 09:34		21/11/2012 09:34		21/11/2012 09:34:00
21/11/2012 08:09		21/11/2012 08:09		21/11/2012 08:09:00
03/11/2012 10:24		03/11/2012 10:24		11/03/2012 10:24
02/10/2012 14:49		02/10/2012 14:49		10/02/2012 14:49
17/10/2012 13:45		17/10/2012 13:45		17/10/2012 13:45:00
25/10/2012 06:11		25/10/2012 06:11		25/10/2012 06:11:00
02/11/2012 13:21		02/11/2012 13:21		11/02/2012 13:21
12/11/2012 14:32		12/11/2012 14:32		11/12/2012 14:32
13/11/2012 11:36		13/11/2012 11:36		13/11/2012 11:36:00
15/11/2012 09:21		15/11/2012 09:21		15/11/2012 09:21:00
06/10/2012 11:24		06/10/2012 11:24		10/06/2012 11:24
18/10/2012 13:00		18/10/2012 13:00		18/10/2012 13:00:00
24/10/2012 09:18		24/10/2012 09:18		24/10/2012 09:18:00
24/10/2012 10:15		24/10/2012 10:15		24/10/2012 10:15:00
18/11/2012 07:14		18/11/2012 07:14		18/11/2012 07:14:00
14/11/2012 07:28		14/11/2012 07:28		14/11/2012 07:28:00
10/10/2012 15:39		10/10/2012 15:39		10/10/2012 15:39
25/10/2012 09:53		25/10/2012 09:53		25/10/2012 09:53:00
29/08/2012 11:46		29/08/2012 11:46		29/08/2012 11:46:00
07/11/2012 15:00		07/11/2012 15:00		11/07/2012 15:00
13/11/2012 17:50		13/11/2012 17:50		13/11/2012 17:50:00
14/11/2012 12:12		14/11/2012 12:12		14/11/2012 12:12:00
08/12/2012 10:27		08/12/2012 10:27		12/08/2012 10:27
10/12/2012 16:51		10/12/2012 16:51		12/10/2012 16:51
10/12/2012 09:57		10/12/2012 09:57		12/10/2012 09:57
11/12/2012 08:00		11/12/2012 08:00		12/11/2012 08:00
10/12/2012 17:03		10/12/2012 17:03		12/10/2012 17:03
10/12/2012 09:48		10/12/2012 09:48		12/10/2012 09:48
08/12/2012 11:39		08/12/2012 11:39		12/08/2012 11:39
10/12/2012 10:06		10/12/2012 10:06		12/10/2012 10:06
09/12/2012 15:01		09/12/2012 15:01		12/09/2012 15:01
09/12/2012 10:53		09/12/2012 10:53		12/09/2012 10:53
07/12/2012 14:47		07/12/2012 14:47		12/07/2012 14:47
04/12/2012 10:56		04/12/2012 10:56		12/04/2012 10:56
04/12/2012 15:52		04/12/2012 15:52		12/04/2012 15:52
07/12/2012 10:19		07/12/2012 10:19		12/07/2012 10:19
03/12/2012 12:46		03/12/2012 12:46		12/03/2012 12:46
03/12/2012 07:35		03/12/2012 07:35		12/03/2012 07:35
03/12/2012 09:09		03/12/2012 09:09		12/03/2012 09:09
06/12/2012 10:22		06/12/2012 10:22		12/06/2012 10:22
30/11/2012 11:37		30/11/2012 11:37		30/11/2012 11:37:00
30/11/2012 09:33		30/11/2012 09:33		30/11/2012 09:33:00
28/01/2012 14:06		28/01/2012 14:06		28/01/2012 14:06:00
04/12/2012 10:20		04/12/2012 10:20		12/04/2012 10:20
Recorded code that works manually is:

Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Anyone come up against this before and have a solution?

thanks in advance