locked
How to sort the tables in a document RRS feed

  • Question

  • Hi I have 400 tables in a document. 

    Each table has several rows and each row has 2 columns.

    I want to sort the tables according to the 2nd column(Heading) in a row in each table.

    Is it possible? (If I use Word VBA, is it possible?)

    Let me know please!

    Thanks for your attention

    Liki

    Wednesday, August 15, 2012 2:11 PM

Answers

  • The following macro sorts tables in a document, based on a comparison of the contents of cell B1. The code inserts an empty paragraph after each table, if necessary, and cuts the empty paragraph after each table with it for relocation via pasting, to ensure the tables don’t get joined together as part of the sort process.

    Sub TableSorter()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long, k As Long
    Dim strVal As String, StrTbls As String
    Dim RngOld As Range, RngNew As Range, bClr As Boolean
    bClr = False
    With ActiveDocument
      If .Tables.Count < 2 Then GoTo CleanUp
      If .Paragraphs.First.Range.Information(wdWithInTable) = True Then
        .Tables(1).Range.Cut
        .Paragraphs.First.Range.InsertBefore vbCr & vbCr
        .Range.Paragraphs.First.Range.Characters.Last.Next.Paste
        bClr = True
      End If
    Restart:
      StrTbls = ""
      For i = 1 To .Tables.Count
        strVal = .Tables(i).Cell(1, 2).Range.Text
        strVal = Left(strVal, Len(strVal) - 2)
        StrTbls = StrTbls & "," & strVal
      Next
      For i = 1 To UBound(Split(StrTbls, ",")) - 1
        For j = i + 1 To UBound(Split(StrTbls, ","))
          If Split(StrTbls, ",")(j) < Split(StrTbls, ",")(i) Then
            Set RngOld = .Tables(j).Range
            With RngOld
              If .Paragraphs.Last.Next.Range.Text = vbCr Then .Paragraphs.Last.Next.Range.Delete
              .Cut
            End With
            Set RngNew = .Tables(i).Range.Paragraphs.First.Previous.Range.Characters.Last
            With RngNew
              .Collapse wdCollapseStart
              .InsertAfter vbCr
              .Collapse wdCollapseEnd
              .Paste
            End With
            GoTo Restart
          End If
        Next
      Next
    CleanUp:
      If bClr = True Then .Paragraphs.First.Range.Delete
    End With
    Set RngOld = Nothing: Set RngNew = Nothing
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]



    • Marked as answer by Liki Crus Sunday, September 9, 2012 1:17 PM
    • Edited by macropodMVP Wednesday, October 2, 2013 7:13 AM Revised code
    Friday, September 7, 2012 11:05 PM

All replies

  • Here is a macro:

    Sub SortTables()
        Dim tbl As Table
        Application.ScreenUpdating = False
        For Each tbl In ActiveDocument.Tables
            tbl.Sort _
                ExcludeHeader:=True, _
                FieldNumber:=2, _
                SortFieldType:=wdSortFieldAlphanumeric, _
                SortOrder:=wdSortOrderAscending
        Next tbl
        Application.ScreenUpdating = True
    End Sub

    This will sort all tables on the second column.

    I have assumed that the first row of each table should not be sorted. If it should be sorted with the rest of the rows, change ExcludeHeader:=True to ExcludeHeader:=False.

    If the tables contain numbers, change SortFieldType:=wdSortFieldAlphanumeric to SortFieldType:=wdSortNumeric.

    And if you want to sort in descending order, change SortOrder:=wdSortOrderAscending to SortOrder:=wdSortOrderDescending.


    Regards, Hans Vogelaar

    • Marked as answer by Max Meng Wednesday, August 29, 2012 12:48 PM
    • Unmarked as answer by Liki Crus Friday, September 7, 2012 7:20 PM
    Wednesday, August 15, 2012 2:33 PM
  • check this link this may help you

    http://www.dummies.com/how-to/content/how-to-sort-a-table-in-word-2007.html

    Thursday, August 16, 2012 12:27 PM
  • While that link has a good explanation of how to sort a table in Word, it won't be of much help if you want to sort 400 tables...

    Regards, Hans Vogelaar

    Thursday, August 16, 2012 12:47 PM
  • Hi Hans,

    Thanks for your reply! But I am sorry that I didn't seem to write what I want correctly.

    Here is an example to show what I want.

    Table1 

    Title: ABCD

    Description: afaf asfaf

    Table2

    Title: CCD

    Description: adsfas adsf 

    Table3

    Title: aaa

    description: afafaf

    I'd like to change the table orders in a document. That's I want to sort the tables with Title' value(column 2).

    Result will be the following

    Table3

    Table1

    Table2

    Is it possible?

    Looking forward to your help.

    Friday, September 7, 2012 7:24 PM
  • The following macro sorts tables in a document, based on a comparison of the contents of cell B1. The code inserts an empty paragraph after each table, if necessary, and cuts the empty paragraph after each table with it for relocation via pasting, to ensure the tables don’t get joined together as part of the sort process.

    Sub TableSorter()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long, k As Long
    Dim strVal As String, StrTbls As String
    Dim RngOld As Range, RngNew As Range, bClr As Boolean
    bClr = False
    With ActiveDocument
      If .Tables.Count < 2 Then GoTo CleanUp
      If .Paragraphs.First.Range.Information(wdWithInTable) = True Then
        .Tables(1).Range.Cut
        .Paragraphs.First.Range.InsertBefore vbCr & vbCr
        .Range.Paragraphs.First.Range.Characters.Last.Next.Paste
        bClr = True
      End If
    Restart:
      StrTbls = ""
      For i = 1 To .Tables.Count
        strVal = .Tables(i).Cell(1, 2).Range.Text
        strVal = Left(strVal, Len(strVal) - 2)
        StrTbls = StrTbls & "," & strVal
      Next
      For i = 1 To UBound(Split(StrTbls, ",")) - 1
        For j = i + 1 To UBound(Split(StrTbls, ","))
          If Split(StrTbls, ",")(j) < Split(StrTbls, ",")(i) Then
            Set RngOld = .Tables(j).Range
            With RngOld
              If .Paragraphs.Last.Next.Range.Text = vbCr Then .Paragraphs.Last.Next.Range.Delete
              .Cut
            End With
            Set RngNew = .Tables(i).Range.Paragraphs.First.Previous.Range.Characters.Last
            With RngNew
              .Collapse wdCollapseStart
              .InsertAfter vbCr
              .Collapse wdCollapseEnd
              .Paste
            End With
            GoTo Restart
          End If
        Next
      Next
    CleanUp:
      If bClr = True Then .Paragraphs.First.Range.Delete
    End With
    Set RngOld = Nothing: Set RngNew = Nothing
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]



    • Marked as answer by Liki Crus Sunday, September 9, 2012 1:17 PM
    • Edited by macropodMVP Wednesday, October 2, 2013 7:13 AM Revised code
    Friday, September 7, 2012 11:05 PM
  • Hi Paul, 

    You can do like this? This is a general sorting!

    dim arr (1000) as string

    for i=1 to 999

    for j=i+1 to j<1000

    temp = arr(i)

    arr(i) = arr(j): arr(j)=temp

    // we will exchange the contents for 2 tables(i, j)

    next j

    next i

    Could you guide me how to exchange the positions of 2 tables(i, j order)?

    Thanks


    Liki Crus

    Sunday, September 9, 2012 6:46 AM
  • Thank you Paul,

    You have put me on the right track.

    For my purpose I need to sort the tables on the contents of a  numerical value in A1. In your code the sorting mechanism is based on text. If using your code the table sorting order looks like 1,11,2,21,22,3 etc but not 1,2,3,11,21,22 etc.

    I cant figure it out how to  adjust your code so that it sorts in a numerical way. Can you point me in the right direction?

    Kind regards,

    Jos

    Tuesday, August 27, 2013 11:05 AM
  • Hi Jos,

    I've just updated the previous code to make it more reliable. With the revised code, all you need to do for your purposes is to change the line:
    StrTbls = StrTbls & "," & strVal
    to:
    StrTbls = StrTbls & "," & Format(strVal, "000")

    Technically, that same change should work with cells containing text but, as it effectively forces a numeric comparison, it could have undesirable results if a textwise comparison is needed, especially if the tables have a mix of numeric and non-numeric data in B1.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, October 2, 2013 7:18 AM