none
[Excel] How to refresh Sort Table RRS feed

  • Question

  • Hi!

    In VSTO / Excel, Is it possible to refresh the table so that it takes into account the sort.

    https://dl.dropbox.com/u/7413995/HowToRefreshSortTable.xlsx
    As you can see in this document: the second column is not sorted despite the sort icon that appears.

    Thank you!



    Monday, January 14, 2013 10:56 AM

Answers

  • Hi Jeremie,

    I've figure out it.

    I've tried Tab.Sort.Apply within Excel VB editor. It seem that the apply method is used like a command to tell the table take the sort action.

    It works fine on my side, so I believe it will work in your project.

    You can take a try and let me know the result.

    HTH.


    Regards,
    Fermin
    What's life without whimsy?


    Tuesday, January 15, 2013 9:47 AM
  • Quist Zhang You are right!

    Here the code C# refresh Filters and Sorts for Tab:

                    foreach (Microsoft.Office.Interop.Excel._Worksheet s in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
                    {
                        foreach (Microsoft.Office.Interop.Excel.ListObject lo in s.ListObjects)
                        {
                                Microsoft.Office.Interop.Excel.Sort so = lo.Sort;
                                if (so != null) so.Apply();
                                Microsoft.Office.Interop.Excel.AutoFilter af = lo.AutoFilter;
                                if (af != null) af.ApplyFilter();
                                lo.Refresh();
                            }
                        }
                    }

    Thanks !




    Thursday, January 17, 2013 9:34 AM

All replies

  • Hi Jeremie,

    Thank you for posting in the MSDN Forum.

    You've mentioned that your project is an VSTO project. Is it a document level customization?

    Could you please provide sample code of your project?

    I've tried to sort the 2nd column manually and record my actions with macro recorder. The below are the code generated by macro recorder.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
        ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields. _
            Clear
        ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort.SortFields. _
            Add Key:=Range("Tableau1[[#All],[Colonne2]]"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau1").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    
    End Sub

    I've reused the macro it works fine. So I think you can post your code here and then we can figure out the solution together.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 15, 2013 5:39 AM
    Moderator
  • Hi Quist Zhang,

    Thanks for your answer !

    I have VSTO40 / Add-In Excel VS2010 C# 4.0 project.

    I use Aspose.Cells Library for generating my document.

    I have a document Model XLSX (witha table thathas a columnsorted), and I merge with Aspose.Cells this model for create a new document.

    It is therefore difficult for me to give you an example code. I see this as visually merged document is active sorting (sort icon in Excel), but in practice the table did not, as you can see in this file: https://dl.dropbox.com/u/7413995/HowToRefreshSortTable.xlsx

    I asked the same question about the site Aspose, they told me that I had to redo the sorting manually! While Excel has a reversal sorting!

    I wonder if there is not just a Tab.Refresh () to execute!

    Thanks !
    Tuesday, January 15, 2013 8:41 AM
  • Hi Jeremie,

    I've figure out it.

    I've tried Tab.Sort.Apply within Excel VB editor. It seem that the apply method is used like a command to tell the table take the sort action.

    It works fine on my side, so I believe it will work in your project.

    You can take a try and let me know the result.

    HTH.


    Regards,
    Fermin
    What's life without whimsy?


    Tuesday, January 15, 2013 9:47 AM
  • In your example, you manually specify sorting (Order: = xlAscending
    ). This is exactly what I do not want :)


    While I have a model which XLSX table has a column sorted.
    I do not want to sort by programming! But merely show the table that appears as visually sorting in Excel! in this document:
    https://dl.dropbox.com/u/7413995/HowToRefreshSortTable.xlsx
    Excel does well appear the sort icon, yet the column is not sorted!


    I just want to do a refresh, without having to specify which column I want to sort

    Tuesday, January 15, 2013 1:55 PM
  • Hi Jeremie,

    Thank you for your last reply.

    As far as I know, there's no Tab.Refresh () method. You can try the suggestion above, use Tab.Sort.Apply method.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 16, 2013 4:28 AM
    Moderator
  • Quist Zhang You are right!

    Here the code C# refresh Filters and Sorts for Tab:

                    foreach (Microsoft.Office.Interop.Excel._Worksheet s in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
                    {
                        foreach (Microsoft.Office.Interop.Excel.ListObject lo in s.ListObjects)
                        {
                                Microsoft.Office.Interop.Excel.Sort so = lo.Sort;
                                if (so != null) so.Apply();
                                Microsoft.Office.Interop.Excel.AutoFilter af = lo.AutoFilter;
                                if (af != null) af.ApplyFilter();
                                lo.Refresh();
                            }
                        }
                    }

    Thanks !




    Thursday, January 17, 2013 9:34 AM
  • Hi Jeremie,

    Glad to hear that you've solved your issue.

    Thank you for sharing your solution which might be very helpful to other community members.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 17, 2013 9:44 AM
    Moderator