Sort method from Javascript automation

[smr78]

Hi,
I try to sort a worksheet range with 3 criterias as allowed by Sort method
programmed in Javascript.
The problem is that the sort is performed on the first and third criteria
but not performed on the second criteria.
As I read on an older post, there is the same problem with Vbscript as the
second criteria is not taken into account when there are 3 criterias.
My simplified code is :

excelapp = new ActiveXObject("Excel.Application");
excelapp.workbooks.open (myfile.xls);
excelapp.activeworkbook.worksheets(mysheetname).select();
xl=excelapp.activesheet;
myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);

Referring to the short Sort method arguments list :
expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
OrderCustom, MatchCase, Orientation, SortMethod)
it seems that the Type argument is not well passed to Excel, since the
following code is effective :
myrange.sort(xl.columns(1),1,xl.columns(2));

As soon the Type argument is listed, the second column is not sorted.

Could someone knows how to pass the Type argument?
The null and "" values are the only values accepted in my tests. An empty
value (as in VBscript) is not accepted.

Thank you for any help.

[Jim Rech]

I found that using "" for the type argument seemed to work, i.e., column s
sorted.

My code:
excelapp = new ActiveXObject("Excel.Application");
excelapp.visible = true;
excelapp.workbooks.open ("c:\myfile.xls");
xl=excelapp.activesheet;
myrange = xl.range("A1:C6");
myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);

--
Jim
"smr78" <[email protected]> wrote in message
news:[email protected]...
| Hi,
| I try to sort a worksheet range with 3 criterias as allowed by Sort method
| programmed in Javascript.
| The problem is that the sort is performed on the first and third criteria
| but not performed on the second criteria.
| As I read on an older post, there is the same problem with Vbscript as the
| second criteria is not taken into account when there are 3 criterias.
| My simplified code is :
|
| excelapp = new ActiveXObject("Excel.Application");
| excelapp.workbooks.open (myfile.xls);
| excelapp.activeworkbook.worksheets(mysheetname).select();
| xl=excelapp.activesheet;
|
myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
|
| Referring to the short Sort method arguments list :
| expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
| OrderCustom, MatchCase, Orientation, SortMethod)
| it seems that the Type argument is not well passed to Excel, since the
| following code is effective :
| myrange.sort(xl.columns(1),1,xl.columns(2));
|
| As soon the Type argument is listed, the second column is not sorted.
|
| Could someone knows how to pass the Type argument?
| The null and "" values are the only values accepted in my tests. An empty
| value (as in VBscript) is not accepted.
|
| Thank you for any help.

[smr78]

Thanks for answering.
It is right that the "" is accepted as Type argument, there is no syntax
error, but the second column is not sorted (in my case), only the first and
third are sorted. Did you checked your second column values?
Regards
Serge

"Jim Rech" wrote:

> I found that using "" for the type argument seemed to work, i.e., column s
> sorted.
>
> My code:
> excelapp = new ActiveXObject("Excel.Application");
> excelapp.visible = true;
> excelapp.workbooks.open ("c:\myfile.xls");
> xl=excelapp.activesheet;
> myrange = xl.range("A1:C6");
> myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);
>
> --
> Jim
> "smr78" <[email protected]> wrote in message
> news:[email protected]...
> | Hi,
> | I try to sort a worksheet range with 3 criterias as allowed by Sort method
> | programmed in Javascript.
> | The problem is that the sort is performed on the first and third criteria
> | but not performed on the second criteria.
> | As I read on an older post, there is the same problem with Vbscript as the
> | second criteria is not taken into account when there are 3 criterias.
> | My simplified code is :
> |
> | excelapp = new ActiveXObject("Excel.Application");
> | excelapp.workbooks.open (myfile.xls);
> | excelapp.activeworkbook.worksheets(mysheetname).select();
> | xl=excelapp.activesheet;
> |
> myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
> |
> | Referring to the short Sort method arguments list :
> | expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
> | OrderCustom, MatchCase, Orientation, SortMethod)
> | it seems that the Type argument is not well passed to Excel, since the
> | following code is effective :
> | myrange.sort(xl.columns(1),1,xl.columns(2));
> |
> | As soon the Type argument is listed, the second column is not sorted.
> |
> | Could someone knows how to pass the Type argument?
> | The null and "" values are the only values accepted in my tests. An empty
> | value (as in VBscript) is not accepted.
> |
> | Thank you for any help.
>
>
>

[Jim Rech]

Try specifying the second field again for the Type arg:

excelapp = new ActiveXObject("Excel.Application");
excelapp.visible = true;
excelapp.workbooks.open ("c:\myfile.xls");
xl=excelapp.activesheet;
myrange = xl.range("A1:C6");
myrange.sort(xl.columns(1),1,xl.columns(2),xl.columns(2),1,xl.columns(3),1,1,1,0,1);

--
Jim
"smr78" <[email protected]> wrote in message
news:[email protected]...
| Thanks for answering.
| It is right that the "" is accepted as Type argument, there is no syntax
| error, but the second column is not sorted (in my case), only the first
and
| third are sorted. Did you checked your second column values?
| Regards
| Serge
|
| "Jim Rech" wrote:
|
| > I found that using "" for the type argument seemed to work, i.e., column
s
| > sorted.
| >
| > My code:
| > excelapp = new ActiveXObject("Excel.Application");
| > excelapp.visible = true;
| > excelapp.workbooks.open ("c:\myfile.xls");
| > xl=excelapp.activesheet;
| > myrange = xl.range("A1:C6");
| >
myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);
| >
| > --
| > Jim
| > "smr78" <[email protected]> wrote in message
| > news:[email protected]...
| > | Hi,
| > | I try to sort a worksheet range with 3 criterias as allowed by Sort
method
| > | programmed in Javascript.
| > | The problem is that the sort is performed on the first and third
criteria
| > | but not performed on the second criteria.
| > | As I read on an older post, there is the same problem with Vbscript as
the
| > | second criteria is not taken into account when there are 3 criterias.
| > | My simplified code is :
| > |
| > | excelapp = new ActiveXObject("Excel.Application");
| > | excelapp.workbooks.open (myfile.xls);
| > | excelapp.activeworkbook.worksheets(mysheetname).select();
| > | xl=excelapp.activesheet;
| > |
| >
myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
| > |
| > | Referring to the short Sort method arguments list :
| > | expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,
Header,
| > | OrderCustom, MatchCase, Orientation, SortMethod)
| > | it seems that the Type argument is not well passed to Excel, since the
| > | following code is effective :
| > | myrange.sort(xl.columns(1),1,xl.columns(2));
| > |
| > | As soon the Type argument is listed, the second column is not sorted.
| > |
| > | Could someone knows how to pass the Type argument?
| > | The null and "" values are the only values accepted in my tests. An
empty
| > | value (as in VBscript) is not accepted.
| > |
| > | Thank you for any help.
| >
| >
| >

[smr78]

That's great, very great.
I never could have think to that. The Excel VBA help says that Type argument
must not be used for a Range sort!
Can you tell us where this solution is documented?
Many thanks
----
Serge

"Jim Rech" wrote:

> Try specifying the second field again for the Type arg:
>
> excelapp = new ActiveXObject("Excel.Application");
> excelapp.visible = true;
> excelapp.workbooks.open ("c:\myfile.xls");
> xl=excelapp.activesheet;
> myrange = xl.range("A1:C6");
> myrange.sort(xl.columns(1),1,xl.columns(2),xl.columns(2),1,xl.columns(3),1,1,1,0,1);
>
> --
> Jim
> "smr78" <[email protected]> wrote in message
> news:[email protected]...
> | Thanks for answering.
> | It is right that the "" is accepted as Type argument, there is no syntax
> | error, but the second column is not sorted (in my case), only the first
> and
> | third are sorted. Did you checked your second column values?
> | Regards
> | Serge
> |
> | "Jim Rech" wrote:
> |
> | > I found that using "" for the type argument seemed to work, i.e., column
> s
> | > sorted.
> | >
> | > My code:
> | > excelapp = new ActiveXObject("Excel.Application");
> | > excelapp.visible = true;
> | > excelapp.workbooks.open ("c:\myfile.xls");
> | > xl=excelapp.activesheet;
> | > myrange = xl.range("A1:C6");
> | >
> myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);
> | >
> | > --
> | > Jim
> | > "smr78" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | Hi,
> | > | I try to sort a worksheet range with 3 criterias as allowed by Sort
> method
> | > | programmed in Javascript.
> | > | The problem is that the sort is performed on the first and third
> criteria
> | > | but not performed on the second criteria.
> | > | As I read on an older post, there is the same problem with Vbscript as
> the
> | > | second criteria is not taken into account when there are 3 criterias.
> | > | My simplified code is :
> | > |
> | > | excelapp = new ActiveXObject("Excel.Application");
> | > | excelapp.workbooks.open (myfile.xls);
> | > | excelapp.activeworkbook.worksheets(mysheetname).select();
> | > | xl=excelapp.activesheet;
> | > |
> | >
> myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
> | > |
> | > | Referring to the short Sort method arguments list :
> | > | expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,
> Header,
> | > | OrderCustom, MatchCase, Orientation, SortMethod)
> | > | it seems that the Type argument is not well passed to Excel, since the
> | > | following code is effective :
> | > | myrange.sort(xl.columns(1),1,xl.columns(2));
> | > |
> | > | As soon the Type argument is listed, the second column is not sorted.
> | > |
> | > | Could someone knows how to pass the Type argument?
> | > | The null and "" values are the only values accepted in my tests. An
> empty
> | > | value (as in VBscript) is not accepted.
> | > |
> | > | Thank you for any help.
> | >
> | >
> | >
>
>
>

[Jim Rech]

>>Can you tell us where this solution is documented?

As far as I know, only in my previous message.<g> I thought it was worth a
try despite Help saying it was only for pivot tables since clearly passing a
null was what was causing the problem. Since we had to pass something and
since Excel 2003 Help said that this parameter "specifies which elements are
to be sorted", I tried it. You really cannot believe what Help says, it's
frequently wrong, sad to say.

--
Jim
"smr78" <[email protected]> wrote in message
news:[email protected]...
| That's great, very great.
| I never could have think to that. The Excel VBA help says that Type
argument
| must not be used for a Range sort!
| Can you tell us where this solution is documented?
| Many thanks
| ----
| Serge
|
| "Jim Rech" wrote:
|
| > Try specifying the second field again for the Type arg:
| >
| > excelapp = new ActiveXObject("Excel.Application");
| > excelapp.visible = true;
| > excelapp.workbooks.open ("c:\myfile.xls");
| > xl=excelapp.activesheet;
| > myrange = xl.range("A1:C6");
| >
myrange.sort(xl.columns(1),1,xl.columns(2),xl.columns(2),1,xl.columns(3),1,1,1,0,1);
| >
| > --
| > Jim
| > "smr78" <[email protected]> wrote in message
| > news:[email protected]...
| > | Thanks for answering.
| > | It is right that the "" is accepted as Type argument, there is no
syntax
| > | error, but the second column is not sorted (in my case), only the
first
| > and
| > | third are sorted. Did you checked your second column values?
| > | Regards
| > | Serge
| > |
| > | "Jim Rech" wrote:
| > |
| > | > I found that using "" for the type argument seemed to work, i.e.,
column
| > s
| > | > sorted.
| > | >
| > | > My code:
| > | > excelapp = new ActiveXObject("Excel.Application");
| > | > excelapp.visible = true;
| > | > excelapp.workbooks.open ("c:\myfile.xls");
| > | > xl=excelapp.activesheet;
| > | > myrange = xl.range("A1:C6");
| > | >
| >
myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);
| > | >
| > | > --
| > | > Jim
| > | > "smr78" <[email protected]> wrote in message
| > | > news:[email protected]...
| > | > | Hi,
| > | > | I try to sort a worksheet range with 3 criterias as allowed by
Sort
| > method
| > | > | programmed in Javascript.
| > | > | The problem is that the sort is performed on the first and third
| > criteria
| > | > | but not performed on the second criteria.
| > | > | As I read on an older post, there is the same problem with
Vbscript as
| > the
| > | > | second criteria is not taken into account when there are 3
criterias.
| > | > | My simplified code is :
| > | > |
| > | > | excelapp = new ActiveXObject("Excel.Application");
| > | > | excelapp.workbooks.open (myfile.xls);
| > | > | excelapp.activeworkbook.worksheets(mysheetname).select();
| > | > | xl=excelapp.activesheet;
| > | > |
| > | >
| >
myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
| > | > |
| > | > | Referring to the short Sort method arguments list :
| > | > | expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,
| > Header,
| > | > | OrderCustom, MatchCase, Orientation, SortMethod)
| > | > | it seems that the Type argument is not well passed to Excel, since
the
| > | > | following code is effective :
| > | > | myrange.sort(xl.columns(1),1,xl.columns(2));
| > | > |
| > | > | As soon the Type argument is listed, the second column is not
sorted.
| > | > |
| > | > | Could someone knows how to pass the Type argument?
| > | > | The null and "" values are the only values accepted in my tests.
An
| > empty
| > | > | value (as in VBscript) is not accepted.
| > | > |
| > | > | Thank you for any help.
| > | >
| > | >
| > | >
| >
| >
| >

[smr78]

You're right, the Help documentation is often hard to read, and a "common"
reader like me is supposed to read "specifies which elements are to be
sorted... in a pivot table".
Many thanks again.
Serge

"Jim Rech" wrote:

> >>Can you tell us where this solution is documented?
>
> As far as I know, only in my previous message.<g> I thought it was worth a
> try despite Help saying it was only for pivot tables since clearly passing a
> null was what was causing the problem. Since we had to pass something and
> since Excel 2003 Help said that this parameter "specifies which elements are
> to be sorted", I tried it. You really cannot believe what Help says, it's
> frequently wrong, sad to say.
>
> --
> Jim
> "smr78" <[email protected]> wrote in message
> news:[email protected]...
> | That's great, very great.
> | I never could have think to that. The Excel VBA help says that Type
> argument
> | must not be used for a Range sort!
> | Can you tell us where this solution is documented?
> | Many thanks
> | ----
> | Serge
> |
> | "Jim Rech" wrote:
> |
> | > Try specifying the second field again for the Type arg:
> | >
> | > excelapp = new ActiveXObject("Excel.Application");
> | > excelapp.visible = true;
> | > excelapp.workbooks.open ("c:\myfile.xls");
> | > xl=excelapp.activesheet;
> | > myrange = xl.range("A1:C6");
> | >
> myrange.sort(xl.columns(1),1,xl.columns(2),xl.columns(2),1,xl.columns(3),1,1,1,0,1);
> | >
> | > --
> | > Jim
> | > "smr78" <[email protected]> wrote in message
> | > news:[email protected]...
> | > | Thanks for answering.
> | > | It is right that the "" is accepted as Type argument, there is no
> syntax
> | > | error, but the second column is not sorted (in my case), only the
> first
> | > and
> | > | third are sorted. Did you checked your second column values?
> | > | Regards
> | > | Serge
> | > |
> | > | "Jim Rech" wrote:
> | > |
> | > | > I found that using "" for the type argument seemed to work, i.e.,
> column
> | > s
> | > | > sorted.
> | > | >
> | > | > My code:
> | > | > excelapp = new ActiveXObject("Excel.Application");
> | > | > excelapp.visible = true;
> | > | > excelapp.workbooks.open ("c:\myfile.xls");
> | > | > xl=excelapp.activesheet;
> | > | > myrange = xl.range("A1:C6");
> | > | >
> | >
> myrange.sort(xl.columns(1),1,xl.columns(2),"",1,xl.columns(3),1,1,1,0,1);
> | > | >
> | > | > --
> | > | > Jim
> | > | > "smr78" <[email protected]> wrote in message
> | > | > news:[email protected]...
> | > | > | Hi,
> | > | > | I try to sort a worksheet range with 3 criterias as allowed by
> Sort
> | > method
> | > | > | programmed in Javascript.
> | > | > | The problem is that the sort is performed on the first and third
> | > criteria
> | > | > | but not performed on the second criteria.
> | > | > | As I read on an older post, there is the same problem with
> Vbscript as
> | > the
> | > | > | second criteria is not taken into account when there are 3
> criterias.
> | > | > | My simplified code is :
> | > | > |
> | > | > | excelapp = new ActiveXObject("Excel.Application");
> | > | > | excelapp.workbooks.open (myfile.xls);
> | > | > | excelapp.activeworkbook.worksheets(mysheetname).select();
> | > | > | xl=excelapp.activesheet;
> | > | > |
> | > | >
> | >
> myrange.sort(xl.columns(1),1,xl.columns(2),null,1,xl.columns(3),1,1,1,0,1);
> | > | > |
> | > | > | Referring to the short Sort method arguments list :
> | > | > | expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3,
> | > Header,
> | > | > | OrderCustom, MatchCase, Orientation, SortMethod)
> | > | > | it seems that the Type argument is not well passed to Excel, since
> the
> | > | > | following code is effective :
> | > | > | myrange.sort(xl.columns(1),1,xl.columns(2));
> | > | > |
> | > | > | As soon the Type argument is listed, the second column is not
> sorted.
> | > | > |
> | > | > | Could someone knows how to pass the Type argument?
> | > | > | The null and "" values are the only values accepted in my tests.
> An
> | > empty
> | > | > | value (as in VBscript) is not accepted.
> | > | > |
> | > | > | Thank you for any help.
> | > | >
> | > | >
> | > | >
> | >
> | >
> | >
>
>
>