Odd date parsing issue

[Guest]

Hi there

Here's another I just can't understand.
I am importing a CSV line by line, then using TextToColumns to delimit the
data.
This works fine, leaving dates in column 'A' often in the format 03-Sep-06.
I perform some autofilters on that column, then copy the visible cells to
another sheet
using:
Sheets("Working").UsedRange.Copy Destination:=Sheets("Temp").Range("A1")

Somwehere along the line, Excel parses the date and results in a timeserial
that corresponds to 09/12/06 - such that no matter what date formatting I
use, the date will always be wrong.

Having stepped through the code isolating parts, the code in question is:
Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth,
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True

The Array(0,4) should specify DMY format, yet it swaps the day / month as
mentioned.

I had inserted this line as the only means I know of to ensure that all
entries in a given column are treated as dates (formatting the column won't
touch string entries etc)... anyone have any useful tips regarding this
action ? Any way of avoiding this for dates where the month / day are
reversible ? I have Date columns with the Date values to the Left AND Right -
I used the above code to convert them to Dates lying to the right... maybe
this is an unnecessary step.
How do you instruct Excel that all values in a column should be treated as
Dates (I had assumed that if this scenario were true the values would align
together on right).

Pointers appreciated

 

[Guest]

I tried it using auto record and got this.

Selection.TextToColumns Destination:=Range("A14"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 4), Array(3, 1)),
TrailingMinusNumbers:=True


One point you write it is a CSV file but you have fixed width not delimited
comma?

If it is delimited adjust and try using the following for field info and
change the delimiter

FieldInfo:=Array(Array(1, 4) )

if it is fixed

FieldInfo:=Array(Array(0, 4) )

and see what happens.

Note the double array.

FieldInfo is an array containing parse information for the individual
columns of data. The interpretation depends on the value of DataType. When
the data is delimited, this argument is an array of two-element arrays, with
each two-element array specifying the conversion options for a particular
column. The first element is the column number (1-based), and the second
element is one of the xlColumnDataType constants specifying how the column is
parsed

XlColumnDataType can be one of these XlColumnDataType constants.

xlGeneralFormat. General
xlTextFormat. Text 1
xlMDYFormat. MDY Date 2
xlDMYFormat. DMY Date 3
xlYMDFormat. YMD Date 4
xlMYDFormat. MYD Date 5
xlDYMFormat. DYM Date 6
xlYDMFormat. YDM Date 7
xlEMDFormat. EMD Date 8 (Taiwan only)
xlSkipColumn. Skip Column 9

The column specifiers can be in any order. If a given column specifier is
not present for a particular column in the input data, the column is parsed
with the General setting.

If the source data has fixed-width columns, the first element of each
two-element array specifies the starting character position in the column (as
an integer; 0 (zero) is the first character). The second element of the
two-element array specifies the parse option for the column as a number from
1 through 9, as listed above.

So try this:

 

[Tom Ogilvy]

with column A holding data like this:

01/05/2006,ABC,123
02/05/2006,ABC,124
03/05/2006,ABC,125
04/05/2006,ABC,126
05/05/2006,ABC,127
06/05/2006,ABC,128
07/05/2006,ABC,129
08/05/2006,ABC,130
09/05/2006,ABC,131
10/05/2006,ABC,132
11/05/2006,ABC,133
12/05/2006,ABC,134
13/05/2006,ABC,135
14/05/2006,ABC,136
15/05/2006,ABC,137
16/05/2006,ABC,138



May 1 - 16, 2006


Sub AAAA()
Columns(1).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 4), _
Array(10, 1), Array(11, 1), _
Array(14, 1), Array(15, 1))

End Sub

worked for me