Page 1 of 1

[Solved] Sorting via macro in Calc

PostPosted: Wed Jul 18, 2018 2:42 pm
by martius
How can I sort in Calc, at same time, four fields (columns) via macro. I need to sort, via macro, four columns, and they need to be coordenated.
It's easy via interface, but I need via macro.
I tried this macro, but it works just to three fiels.

Code: Select all   Expand viewCollapse view
Sub ordenaDados(oDoc As object, oPlan As Object, Ci%, Li%, OrdemCol1, OrdemCol2, OrdemCol3, OrdemCol4)
   Dim Despachante As Object, Controle As Object
   Despachante = createUnoService("")
   Controle = oDoc.CurrentController
   Controle.Select( oPlan.GetCellByPosition(Ci,Li) )
   Dim args(13) As New
      args(0).Name   = "ByRows"      
      args(0).Value  = True
      args(1).Name   = "HasHeader"      
      args(1).Value  = False      
      args(2).Name   = "CaseSensitive"   
      args(2).Value  = False
      args(3).Name   = "NaturalSort"   
      args(3).Value  = False
      args(4).Name   = "IncludeAttribs"   
      args(4).Value  = True
      args(5).Name   = "UserDefIndex"   
      args(5).Value  = 0   
      args(6).Name   = "Col1"      
      args(6).Value  = OrdemCol1      
      args(7).Name   = "Ascending1"   
      args(7).Value  = True
      args(8).Name   = "Col2"      
      args(8).Value  = OrdemCol2      
      args(9).Name   = "Ascending2"   
      args(9).Value  = True
      args(10).Name  = "Col3"      
      args(10).Value = OrdemCol3      
      args(11).Name  = "Ascending3"   
      args(11).Value = True
      args(12).Name  = "Col4"      
      args(12).Value = OrdemCol4      
      args(13).Name  = "Ascending4"   
      args(13).Value = True
   Despachante.executeDispatch(Controle, ".uno:DataSort", "", 0, args())
End Sub

Re: Sorting via macro in Calc

PostPosted: Wed Jul 18, 2018 5:04 pm
One way to sort on four columns is to first sort on the one that would be the last in the sequence and then sort on the other three.
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCellrange = oSheet.getCellrangeByName("B1:E17")
'Want to sort the columns in the order B, D, E, C
'Do the first sort on the column that would be the fourth one to be sorted
Dim aSortFields(0) as New
aSortFields(0).Field = 1   'index = 1 of the CellRange = column C
aSortFields(0).IsAscending = "True"

SortDesc = oCellrange.createSortDescriptor
'A SortDescriptor is an array of 9 propertyvalues: 0 = IsSortColumns, 1 = ContainsHeader,
'2 = MaxFieldCount (set to 3), 3 = SortFields, 4 = BindFormatToContent, 5 = CopyOutputData,
'6 = OutputPosition, 7 = IsUserListEnabled, 8 = UserListIndex
SortDesc(1).Value = TRUE 'ContainsHeader
SortDesc(3).Value = aSortFields() 'SortFields

'Sort the remaining three columns in the order B, D, E
Dim aSortFields2(2) as New
aSortFields2(0).Field = 0   'index = 0 of the CellRange = column B
aSortFields2(0).IsAscending = "True"
aSortFields2(1).Field = 2  'index = 2 of the CellRange = column D
aSortFields2(1).IsAscending = "True"
aSortFields2(2).Field = 3   'index = 3 of the CellRange = columna E
aSortFields2(2).IsAscending = "True"

SortDesc(3).Value = aSortFields2() 'SortFields


After running the macro in the attached file, you can do Edit -> Undo twice to get back to the original order of the items.

Re: [Solved] Sorting via macro in Calc

PostPosted: Thu Jul 19, 2018 1:11 am
by martius
I tried the idea of FJCC (sorting first the last column and then the other three), and it works great.
Thanks a lot!