Automating Import of Date Information
 

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

Automating Import of Date Information
 

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

Automating Import of Date Information
 

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