Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i want to sort individual field separately as descending order by using macros (VBScript)
how i can do that?
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 pEnd Sub
1 will refer to the first expression, 2 to the second and so on.
Hope that helps.
Miguel
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
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
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.
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 pEnd Sub
1 will refer to the first expression, 2 to the second and so on.
Hope that helps.
Miguel
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