External sort


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.


Template


Output

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
Advertisements

Author: Dragos Cojocari

Architect for Rational Publishing Engine

2 thoughts on “External sort”

  1. When I use this macro, it places the column header at the bottom, it does not exclude the header from the sort.

    1. Hey Lew,

      have you set the as header row as being “header” in the RPE template? You do this by setting the row property “row repeat at page beginning” to true.

      Regards,
      Dragos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s