xlSortTextAsNumbers inconsistent? Excel 2003
 

Hi All,

I am getting inconsistent behaviour on data between recording and running a
macro.

If I record a macro that selects all data, then sorts by an ID number (in
column A) and a date (in column B), I get asked the question about whether
Excel should treat everything as a number even though it thinks some things
are text, or sort numbers and text differently. I select treat all as
number, and the sorting works correctly - in UK formatting, 13/05/2005 is
before 02/06/2005 (assuming the same ID number).

The code generated is:

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortTextAsNumbers


However, if I RUN that code, the reverse is true - the "real date" field
(the serialized number, 38xxx that is "true") seems to be used for the
comparison.

Am I missing something obvious that should go in the VBA code here?

Many thanks,

Dave

xlSortTextAsNumbers inconsistent? Excel 2003
 

Looks like this isn't the root of the problem - the root is that double
clicking on a csv has a different outcome than a
Workbooks.Open(fileNameVariable) call.

So I'm trying to figure out a way of opening within Excel that matches
opening manually by double clicking - any ideas?

Thanks,

Dave

"David Evans" wrote in message
...
Hi All,

I am getting inconsistent behaviour on data between recording and running
a macro.

If I record a macro that selects all data, then sorts by an ID number (in
column A) and a date (in column B), I get asked the question about whether
Excel should treat everything as a number even though it thinks some
things are text, or sort numbers and text differently. I select treat all
as number, and the sorting works correctly - in UK formatting, 13/05/2005
is before 02/06/2005 (assuming the same ID number).

The code generated is:

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortTextAsNumbers


However, if I RUN that code, the reverse is true - the "real date" field
(the serialized number, 38xxx that is "true") seems to be used for the
comparison.

Am I missing something obvious that should go in the VBA code here?

Many thanks,

Dave