You might have run into this issue when designing documents with RPE: you want to sort the data but the property you need is not available in the sort dialog.
If your data is organized in tables and your output type is Microsoft Word than you can use Microsoft Word’s sorting capabilities to do the sorting.
Making a sort macro is quite easy, see below:
Sub sortTables() Dim tbl As Table ' sort each table For Each tbl In ActiveDocument.Tables tbl.Select Selection.Sort ExcludeHeader:=true, FieldNumber:="Column 2", SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending Next End Sub
The challenge is to make a macro that will sort only tables that need sorting, can “figure out” what is the column to do the sorting on and also determine if the table has a header or not.
All that is provided in the sortTable macro from the attached document Sort Macro. The logic of the macro and of its matching template is:
- the macro iterates through all the tables from the document’s body
- for each table verify if any of the cells from the first row have a <RPE SORT>comment attached
- if such comment is found that column will be used for sorting the table
- the macro can also delete the comment so that it does not remain in the final output
- if no such comment is found, the table will not be sorted
So when you want to sort a table but RPE won’t allow it, what you need to do is mark the sort column with a <RPE SORT> comment and have RPE run the macro.
The full source code of the macro is:
' Macro: sort tables ' Author: Dragos Cojocari ' This macro is provided as is with no implicit or explicit support from IBM. Sub sortTables() Dim tbl As Table ' sort each table For Each tbl In ActiveDocument.Tables ' determine if the table has header Dim hasheader As Boolean hasheader = False If tbl.Rows.First.HeadingFormat = True Then hasheader = True End If ' get the column to do the sorting on. The column is identified by a comment with the "<RPE_SORT>" content in its first cell Dim hcell As Cell Dim index As Integer pos = 0 For Each hcell In tbl.Rows.First.Cells hcell.Select If Selection.Comments.Count > 0 Then If Selection.Comments.Item(1).Range.Text = "<RPE_SORT>" Then pos = hcell.ColumnIndex ' Delete the comment - remove comment from the line below ' Selection.Comments.Item(1).Delete Exit For End If End If Next ' sorts the table using the found column If pos > 0 Then Dim fldnum As String fldnum = "Column " + CStr(pos) Debug.Print "Sorting on: "; fldnum tbl.Select Selection.Sort ExcludeHeader:=hasheader, FieldNumber:=fldnum, SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending End If Next End Sub