Automating Import of Date Information

[Andrew]

Dear All,

Help would be gratefully received for the following:

I want to import data from a csv file.
When I do this manually (using File -> Open -> Text Import Wizard etc)
the date data comes in fine.
When I automate the process (by recording my manual actions and playing
the recorded macro) the date data is corrupted.

If you want to try it out then:
1) Save the following lines in a txt file. (note the dates are in
dd/mm/yyyy hh:mm format)
"User","Issue","Date"
"Andrew","a","10/01/2006 00:00:00"
"Andrew","b","31/01/2006 00:00:00"
"Andrew","c","11/02/2006 00:00:00"

2)
Record a macro opening the text file in Excel, comma-delimited, with "
as the text qualifier.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 23/05/2006 by Andrew Cox
'

'
Workbooks.OpenText Filename:= _
"C:\Apps\test.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))
Columns("C:C").EntireColumn.AutoFit
Range("C2:C4").Select
Selection.NumberFormat = "d-mmm"
End Sub

3) When you record the macro I think you will get the following dates
appearing:
10-Jan
31-Jan
11-Feb

4) But, when you play the macro (after closing the file), I think you
will get (like me!):
01-Oct
31/01/2006 00:00:00
02-Nov

Clearly, I'd like the first result rather than the second.

Any ideas how to correct this would be very welcome.

Thanks,

Andrew

[Tom Ogilvy]

You need to change the extension of the file from CSV to TXT. When it is
named CSV, excel ignores your settings in the OpenText method.

--
Regards,
Tom Ogilvy


"Andrew" wrote:

> Dear All,
>
> Help would be gratefully received for the following:
>
> I want to import data from a csv file.
> When I do this manually (using File -> Open -> Text Import Wizard etc)
> the date data comes in fine.
> When I automate the process (by recording my manual actions and playing
> the recorded macro) the date data is corrupted.
>
> If you want to try it out then:
> 1) Save the following lines in a txt file. (note the dates are in
> dd/mm/yyyy hh:mm format)
> "User","Issue","Date"
> "Andrew","a","10/01/2006 00:00:00"
> "Andrew","b","31/01/2006 00:00:00"
> "Andrew","c","11/02/2006 00:00:00"
>
> 2)
> Record a macro opening the text file in Excel, comma-delimited, with "
> as the text qualifier.
>
> Sub Macro3()
> '
> ' Macro3 Macro
> ' Macro recorded 23/05/2006 by Andrew Cox
> '
>
> '
> Workbooks.OpenText Filename:= _
> "C:\Apps\test.txt", Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
> _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
> Semicolon:=False _
> , Comma:=True, Space:=False, Other:=False,
> FieldInfo:=Array(Array(1, 1), _
> Array(2, 1), Array(3, 1))
> Columns("C:C").EntireColumn.AutoFit
> Range("C2:C4").Select
> Selection.NumberFormat = "d-mmm"
> End Sub
>
> 3) When you record the macro I think you will get the following dates
> appearing:
> 10-Jan
> 31-Jan
> 11-Feb
>
> 4) But, when you play the macro (after closing the file), I think you
> will get (like me!):
> 01-Oct
> 31/01/2006 00:00:00
> 02-Nov
>
> Clearly, I'd like the first result rather than the second.
>
> Any ideas how to correct this would be very welcome.
>
> Thanks,
>
> Andrew
>
>

[Andrew]

Tom,

Thanks for the reply, but at the moment the file already has a .txt
extension (see the macro code created).

Apologies if speaking of the file as a CSV lead to the confusion.

Thanks,

Andrew