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
|