Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing txt file and wrong date format... MACRO vs MANUAL
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing txt file and wrong date format... MACRO vs MANUAL
In your filed array definition use the following.....
FieldInfo:=Array(Array(1, 4), Array(2, 2), Array(3, 2), Array(4, 2) -- Cheers Nigel wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
importing txt file and wrong date format... MACRO vs MANUAL
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 : In your filed array definition use the following..... FieldInfo:=Array(Array(1, 4), Array(2, 2), Array(3, 2), Array(4, 2) -- Cheers Nigel wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
from date format convert to text format is wrong | Excel Discussion (Misc queries) | |||
date format changes when I save to CSV via a macro, but not manual | Excel Discussion (Misc queries) | |||
Macro gives the wrong date format | Excel Programming | |||
How do I change the date format when importing a txt file? | Excel Worksheet Functions | |||
Macro with manual file selection? | Excel Programming |