Re: How to sort a column in a different sheet ?

[Govert van Drimmelen]

Hi Iulian,

From your question, I guess the sorting works fine when you are on the
same sheet?
If so, you might have to activate the sheet before you are able to
sort (and then reactivate the sheet the user was on at the end of your
macro). Quite a few of the C API commands seem to have this kind of
restriction.

You might like to try the ExcelSelectionHelper class from here
https://exceldna.codeplex.com/wikipage?title=Async%20macro%20example%20-%20formatting%20the%20calling%20cell%20from%20a%20UDF
to wrap your call with. It will reset the sheet state upon Dispose()
so you can change the sheet etc. in a using scope.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *



On May 22, 5:29 pm, Iulian <iulia...@gmail.com> wrote:
> I want to sort a column in a different sheet (possibly a different workbook
> also)
>
> I'm reading a formula (it'a a VLOOKUP) using:
>
> formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, cell);
>
> and then extracting the table_array parameter from that
>
> var parameters = formulaR1C1.Replace("=VLOOKUP(", "");
> parameters = parameters.Substring(0, parameters.Length - 1);var paramArray = parameters.Split(',');
>
> var table_array = paramArray[1];
>
> Now I want to sort ascending by the first column in table_array, and I'm using
>
>  XlCall.Excel(XlCall.xlcSort, 1,table_array , 1);
>
> but that is throwing an error.
>
> If instead of table array I use a reference in the current sheet it's working, for example
>
>  XlCall.Excel(XlCall.xlcSort, 1, "C1", 1); it's working fine.

[Terry Aney]

Trying to figure out how to sort a range by a certain column...given the example here, I don't see where he passes in the 'table array 'range''...just the first cell of the 'sort column'.

Any ideas?  The Excel 4 macro reference syntax for Sort doesn't seem to mention a 'target' range.

[Govert van Drimmelen]

Hi Terry,

The documentation for the SORT (xlcSort) command says:

"Equivalent to choosing the Sort command from the Data menu. Sorts the rows or columns of the selection according to the contents of a key row or column within the selection. Use SORT to rearrange information into ascending or descending order."

I think this means that you have to select the range you want to sort first. With the C API, you can select cells using the SELECT command (xlcSelect).

There are some tricks with xlcSelect - e.g. you have to activate the right sheet first.

You can use some code like this to change the selection, and then reset it after doing the work (e.g. in a using block):

    // Select an ExcelReference (perhaps on another sheet) allowing changes to be made there.

    // On clean-up, resets all the selections and the active sheet.

    // Should not be used if the work you are going to do will switch sheets, make new sheets etc.

    public class ExcelSelectionHelper : XlCall, IDisposable

    {

        object oldSelectionOnActiveSheet;

        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;

        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)

        {

            // Remember old selection state on the active sheet

            oldSelectionOnActiveSheet = Excel(xlfSelection);

            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select

            string refSheet = (string)Excel(xlSheetNm, refToSelect);

            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select

            oldSelectionOnRefSheet = Excel(xlfSelection);

            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection

            Excel(xlcFormulaGoto, refToSelect);

        }

        public void Dispose()

        {

            // Reset the selection on the target sheet

            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            // Reset the sheet originally selected

            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);

            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            // Reset the selection in the active sheet (some bugs make this change sometimes too)

            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);

        }

    }

Then call this as 

using (new ExcelSelectionHelper(myReference))

{

    // myReference is now active

}

-Govert

[Terry Aney]

You're right.  FYI, as a timing of C API vs Interop calls (pretty comparable...which isn't surprising I guess):

ExcelReference dataRange = ... get range...

ExcelReference keyRange = ... first cell of column to sort ...

XlCall.Excel( XlCall.xlcFormulaGoto, dataRange ); // select the range before sort call

XlCall.Excel( XlCall.xlcSort, 1 /* Top To Bottom */, keyRange.GetReference(), 2 /* Descending */ );

Interop: Sort Ascending 14,999/111 Cells - 0.2849211

Interop: Sort Ascending 14,999/111 Cells - 0.2937582

Interop: Sort Ascending 14,999/111 Cells - 0.2902067

Interop: Sort Ascending 14,999/111 Cells - 0.2844797

Interop: Sort Ascending 14,999/111 Cells - 0.2927279

MSExcel.Range dataRange = ... get range ...

MSExcel.Range keyRange = ... first cell of column to sort ...

dataRange.Sort( keyRange, MSExcel.XlSortOrder.xlDescending, comMissing, comMissing, MSExcel.XlSortOrder.xlAscending, comMissing, MSExcel.XlSortOrder.xlAscending, MSExcel.XlYesNoGuess.xlNo, comMissing, comMissing, MSExcel.XlSortOrientation.xlSortColumns );

C API: Sort Descending 14,999/111 Cells - 0.2798459

C API: Sort Descending 14,999/111 Cells - 0.2832656

C API: Sort Descending 14,999/111 Cells - 0.2875362

C API: Sort Descending 14,999/111 Cells - 0.2951797

C API: Sort Descending 14,999/111 Cells - 0.287362

Thanks.

[Govert van Drimmelen]

Hi Terry,

Yes - it's not too surprising that the interop can the C API would perform the same in this case.

I wonder if you could post the timings you made for reading the text from cells via xlfGetCell vs. the COM object model?

Regards,

Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Terry Aney [terry...@gmail.com]
Sent: 12 December 2015 11:28 PM
To: Excel-DNA
Subject: [ExcelDna] Re: How to sort a column in a different sheet ?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Terry Aney]

Yes, going to post that on original thread: https://groups.google.com/forum/#!topic/exceldna/mYPaWZWTLfY

[Terry Aney]

Question about your ExcelSelectionHelper code.  In the comment it says should not be used if the work you are going to do will switch sheets... but the code looks like it handles that?

[Govert van Drimmelen]

It looks like the code assumes that the sheet that is active before your work starts is also the active sheet afterwards. So if your macro will leave another sheet as the active one, it might go wrong.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Terry Aney [terry...@gmail.com]

Sent: 13 December 2015 08:42 PM

To: Excel-DNA
Subject: [ExcelDna] Re: How to sort a column in a different sheet ?

Question about your ExcelSelectionHelper code.  In the comment it says should not be used if the work you are going to do will switch sheets... but the code looks like it handles that?

[Terry Aney]

Posted some speed timings to the aforementioned post.

On Sunday, December 13, 2015 at 1:07:56 PM UTC-6, Govert van Drimmelen wrote:

It looks like the code assumes that the sheet that is active before your work starts is also the active sheet afterwards. So if your macro will leave another sheet as the active one, it might go wrong.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Terry Aney [terry...@gmail.com]
Sent: 13 December 2015 08:42 PM
To: Excel-DNA
Subject: [ExcelDna] Re: How to sort a column in a different sheet ?

Question about your ExcelSelectionHelper code.  In the comment it says should not be used if the work you are going to do will switch sheets... but the code looks like it handles that?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To post to this group, send email to exc...@googlegroups.com.