Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Tables via VBA (Word XP)

    Here's one I hope someone can help me with:

    I have a table with an unknown number of rows. The first cell in each row contains an outline number in the format 1.1.1, 1.1.2, 1.1.3, etc. The rows are not necessarily in "outline order," nor are there always three levels to each number (e.g., it might be 1.1.1, 1.1.2, 2.1, 2.2.1, etc.)

    My problem is that I can't find a way to sort the table so that the rows are in proper order. If I use a numeric sort, I get (for example) 1.1.1, 1.1.10, 1.1.11, 1.1.12, 1.2.1, etc. If I use a text-sort, I still get 1.1.10 before 1.1.2.

    Does anyone know of a method within Word that will sort such a table?
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Tables via VBA (Word XP)

    Edited by Phil Rabichow to add last paragraph
    Hi Mike:
    I don't know if someone has a VBA way to sort the table, but the problem is because Word sorts one character at a time. Personally, I think it's poor design, but apparently the only workaround is to place a zero before the "2" in 1.1.2 etc.; i.e. you have to make the numbers after the second period 2 digits. I also find the sortting mechanism somewhat buggy for numbers. See <!mskb=212116>Microsoft Knowledge Base Article 212116<!/mskb> & <!mskb=214211>Microsoft Knowledge Base Article 214211<!/mskb>.

    I think I just discovered another workaround, although it's a bit of a hassle.
    1. Convert the table to text.
    2. Select all the paragraphs & choose Table/Sort/Options.
    3. Choose the period as the delimiter.
    4. In the main Sort dialog box, sort by Field 1, then by Field 2, then by Field 3---all as numbers.
    5. Convert the text to table, with tabs as the delimiter.
    Cheers,

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Tables via VBA (Word XP)

    There may not be an answer per se, but you gave me the piece of information that I needed.

    What I ended up doing was writing two routines so that I could use Word's own alphanumeric sort. Based on what you said about padding each "digit" with zero's (to make them all the same length), I wrote two routines. The first routine pads each number in the text-range of a cell by using Format(string,"00"). This has the effect of changing "1.1.10" into "01.01.10", while the second routine uses the Val and Trim functions to change them back. In between calling the two routines, I just programmed Word to do a normal (?) alphanumeric sort. And because the routines work with ranges in a For/Each context, rather than with a selection object, they run very fast.

    Thanks for the tip, Phil!
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Tables via VBA (Word XP)

    Hi Mike:
    Glad I could help. If you don't mind, could you attach your code (or a relevant portion of it) here so others could use it? This is a recurring problem.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code for Sorting Tables via VBA (Word XP)

    Sure. Glad to share. If it's OK, i'll just paste it here:

    'The Appendix sort method
    Dim aCell as Cell, aWord as Range, fred as String

    For Each aCell In Selection.Tables(1).Columns(1).Cells
    For Each aWord In aCell.Range.Words
    If IsNumeric(aWord.Text) = True And Len(aWord) = 1 Then
    aWord.Text = Format(aWord.Text, "00")
    End If
    Next
    Next

    'Now you can sort
    ActiveDocument.Tables(1).Select
    Selection.Sort ExcludeHeader:=False, FieldNumber:="Column 1", _
    SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending

    'And now remove the extra zeros
    For Each aCell In Selection.Tables(1).Columns(1).Cells
    If aCell.Range.Words.Count > 1 Then 'I use an exact count because a For/Each using the words collection put me in an infinite loop
    For X = 1 To aCell.Range.Words.Count
    fred = aCell.Range.Words(X)
    If IsNumeric(fred) = True Then
    fred = Trim(Str(Val(fred)))
    aCell.Range.Words(X) = fred
    End If
    Next
    End If
    Next
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Sorting Tables via VBA (Word XP)

    Hi Fred:
    Thanks for posting it. One variable needs defining (I have Option Explicit set), so I made the first line:

    Dim aCell As Cell, aWord As Range, fred As String, X As Integer

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Sorting Tables via VBA (Word XP)

    X needs to be type Long because Count returns a Long.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Sorting Tables via VBA (Word XP)

    Thanks, Howard. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •