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.

 

[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