Sort individual field in straight table by descending order using macro

[udaya_kumar]

Hi,

i want to sort individual field separately as descending order by using macros (VBScript)

how i can do that?

[Anonymous]

Hi

     Please see the attached file and you can change the qv file based on your requirement

     Hope this will helpful for  your problem

Regards

Ashok

[Miguel_Angel_Baeyens]

Hi,

Check this thread and the file attached to see a working example on a macro to sort in a chart.

Hope that helps.

Miguel

[udaya_kumar]

Hi Miguel and chand,

thanks for the reply,

i need to sort the expression field by descending order in the straight table, not the dimensions.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

There may be some misunderstanding here. Expressions are results, not values, and cannot be sorted. What you do can do is sort a dimension using an expression, so, for example, in a chart that displays all your customers and their corresponding current year sales, you can sort the dimension Customer by the Sum(Amount) of sales ascending or descending, although you double click on the expression to sort.

Add the following code:

Sub SortChart
 Set ch = ActiveDocument.GetSheetObject("CH01")
 ch.SortBy 1 '' this line specifies the expression to sort by
 
 Set p = ch.GetProperties
 Set dims = p.Dimensions
 '' Dimensions start by 0
 '' 0 = Disable sort
 '' 1 = Sort ascending
 '' -1 = Sort descending
 i = dims.Item(0).SortCriteria.SortByNumeric
 If i = 1 Then
  dims.Item(0).SortCriteria.SortByNumeric = -1
 Else
  dims.Item(0).SortCriteria.SortByNumeric = 1
 End If
 ch.SetProperties p
End Sub

1 will refer to the first expression, 2 to the second and so on.

Hope that helps.

Miguel

[#6]

Hi Miguel and Chand,

Dimension is getting sorted based on the expression value. But it is toggling between ascending order and descending order. I want to set it to descending order only. Can it be done ??

I m using this macro

Sub SortChart
Set ch = ActiveDocument.GetSheetObject("CH05")
ch.SortBy 1
Set ch = ActiveDocument.GetSheetObject("CH15")
ch.SortBy 1
Set p = ch.GetProperties
Set dims = p.Dimensions
i = dims.Item(0).SortCriteria.SortByNumeric= -1
ch.SetProperties p
End Sub

[Miguel_Angel_Baeyens]

Hi,

You are using two charts to sort by but the dimension is only in one of them. Something seems to be wrong there, or at least make little sense, since the "dims" variable stores the dimension of the CH15 chart, and not the CH05. Maybe is that driving the macro interpreter crazy?

Regards.

Miguel