importing txt file and wrong date format... MACRO vs MANUAL

[a.lagolep]

Hi,

It looks like a lot of people already encountered that kind of
problem.... but I didn't find any solution to the problem.....

Let's say I have got a TXT file that contains this kind of data :

03/10/06 11:53:54;champ1;champ2;champ3
29/09/06 16:09:48;champ1;champ2;champ3
14/09/06 06:24:35;champ1;champ2;champ3
08/09/06 10:56:45;champ1;champ2;champ3
......


if I import manually, no problem. All dates ar ein the right format..;
If now I record and replay the macro during this process.... Lines 1
and 4 have a date format (but the date has been switched to the
american format....), and lines 2 & 3 have not been recognized as date
format.. (they are left justified, like text)
here is the result :

10/03/2006 11:53 champ1 champ2 champ3
29/09/06 16:09:48 champ1 champ2 champ3
14/09/06 06:24:35 champ1 champ2 champ3
09/08/2006 10:56 champ1 champ2 champ3


Does anybody have any clue ?
As an additional info, by playing and changing the format of the first
column, I can have all 4 lines with TXT format... But I would then need
a macro that would convert the whole column to the right date
format.....

FYI, here is the generated code:

Workbooks.OpenText Filename:= _
fileToOpen, Origin:=437, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array( _
3, 1), Array(4, 1)), TrailingMinusNumbers:=True


Thks,,


AL.

 

[Nigel]

In your filed array definition use the following.....

FieldInfo:=Array(Array(1, 4), Array(2, 2), Array(3, 2), Array(4, 2)

 

[a.lagolep]

Hi,

I finally found the answer : adding local=true. parameter

If I just play with the array parameter....(1,4), I get some dates
recognized as date, but some others remain text..

like :
10/03/2006 11:53 champ1 champ2 champ3
29/09/06 16:09:48 champ1 champ2 champ3
14/09/06 06:24:35 champ1 champ2 champ3
09/08/2006 10:56 champ1 champ2 champ3


Thks,

AL.


Nigel a écrit :