Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating Address Information based on selecting data via a validation list | Excel Worksheet Functions | |||
XIRR - Automating the date range | Excel Discussion (Misc queries) | |||
Automating import of Word tables into Excel | Excel Discussion (Misc queries) | |||
Automating data import and separation | Excel Programming | |||
Automating import of a certain type of 'txt' file | Excel Programming |