MS Office Forum / Word / Programming / February 2006
DownAndAccrossTableSorter
|
|
Thread rating:  |
Greg Maxey - 07 Feb 2006 23:43 GMT I scrathed together the following today in response to a post in the tables group. I searched Google groups for a similiar solution and since I didn't see anything, I wanted to share it here. As alwasy constructive criticism is welcomed.
Objective is to sort a word table down and accross. Put cursor is table to be sorted and run the following.
Option Explicit Sub DownAndAccrossTableSorter() Dim i As Long Dim j As Long Dim k As Long Dim oCell As Cell Dim oTmpTable As Table Dim oRng As Word.Range i = Selection.Tables(1).Range.Cells.Count 'Insert a temporary 1 column/multi-row table at the end of the document With ActiveDocument.Paragraphs.Last .Range.Paragraphs.Add .Range.Tables.Add .Range, i, 1 End With 'Define this table Set oTmpTable = ActiveDocument.Tables(ActiveDocument.Range.Tables.Count) 'Fill oTmpTable with contents of table to be sorted For Each oCell In Selection.Tables(1).Range.Cells With oTmpTable .Cell(i, 1).Range.Text = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) End With i = i - 1 Next 'Sort oTmpTable.Sort 'Redefine selected table contents based on sort With Selection.Tables(1) For i = 1 To .Range.Columns.Count For j = 1 To .Range.Rows.Count k = k + 1 Set oRng = oTmpTable.Cell(k, 1).Range .Cell(j, i).Range.Text = Left(oRng.Text, Len(oRng.Text) - 2) Next j Next i End With 'Clean up. oTmpTable.Delete Set oRng = Nothing Set oTmpTable = Nothing End Sub
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
Jezebel - 08 Feb 2006 00:51 GMT Neat.
The purist in me would prefer that the temp table be in a separate document. There are document layouts that would get messed up if you arbitrarily tacked a table on the end. It also makes cleaning up easier: simply discard the temporary document.
Dim pTempDoc as Word.Document set pTempDoc = Documents.Add(Visible:=False) set pTempTable = pTempDoc.Tables.Add(Range:=pTempDoc.Content, _ NumRows:=1, _ NumColumns:=SourceTable.Range.Cells.Count)
pTempDoc.Close SaveChanges:=false
Since your tables have the same number of cells, a more efficient way of copying is like this --
Dim pCell1 As Word.Cell Dim pCell2 As Word.Cell
Set pCell1 = Table1.Cell(1, 1) Set pCell2 = Table2.Cell(1, 1) Do pCell2.Range = Left$(pCell1.Range, Len(pCell1.Range) - 2) Set pCell1 = pCell1.Next Set pCell2 = pCell2.Next Loop Until pCell1 Is Nothing
You could set this up as a separate function, pass the tables as arguments, then use it for both copies.
I scrathed together the following today in response to a post in the tables group. I searched Google groups for a similiar solution and since I didn't see anything, I wanted to share it here. As alwasy constructive criticism is welcomed.
Objective is to sort a word table down and accross. Put cursor is table to be sorted and run the following.
Option Explicit Sub DownAndAccrossTableSorter() Dim i As Long Dim j As Long Dim k As Long Dim oCell As Cell Dim oTmpTable As Table Dim oRng As Word.Range i = Selection.Tables(1).Range.Cells.Count 'Insert a temporary 1 column/multi-row table at the end of the document With ActiveDocument.Paragraphs.Last .Range.Paragraphs.Add .Range.Tables.Add .Range, i, 1 End With 'Define this table Set oTmpTable = ActiveDocument.Tables(ActiveDocument.Range.Tables.Count) 'Fill oTmpTable with contents of table to be sorted For Each oCell In Selection.Tables(1).Range.Cells With oTmpTable .Cell(i, 1).Range.Text = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) End With i = i - 1 Next 'Sort oTmpTable.Sort 'Redefine selected table contents based on sort With Selection.Tables(1) For i = 1 To .Range.Columns.Count For j = 1 To .Range.Rows.Count k = k + 1 Set oRng = oTmpTable.Cell(k, 1).Range .Cell(j, i).Range.Text = Left(oRng.Text, Len(oRng.Text) - 2) Next j Next i End With 'Clean up. oTmpTable.Delete Set oRng = Nothing Set oTmpTable = Nothing End Sub
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
Greg Maxey - 08 Feb 2006 03:40 GMT Jezebel,
Thanks for you comments. I like the temp document idea and have incorporated it in the code shown below.
I played with the pCell1 and pCell2 suggestion a bit, but it want to write to the cells left to right/top to bottom vice top to bottom/left to right. The idea is to have the sort top to bottom/left to right.
Am I missing something?
As alwasy thanks again for sharing your suggestions.
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> Neat. > [quoted text clipped - 82 lines] > Set oTmpTable = Nothing > End Sub Greg Maxey - 08 Feb 2006 03:42 GMT What a dolt!!
Here is the code:
Sub DownThenAccrossTableSorter() Dim i As Long Dim j As Long Dim k As Long Dim oCell As Cell Dim pTmpDoc As Word.Document Dim pTmpTable As Table Dim oRng As Word.Range Dim SourceTable As Table Set SourceTable = Selection.Tables(1) i = SourceTable.Range.Cells.Count 'Insert a temporary 1 column/multi-row table in a temporary document Set pTmpDoc = Documents.Add(Visible:=False) Set pTmpTable = pTmpDoc.Tables.Add(Range:=pTmpDoc.Content, _ NumRows:=i, NumColumns:=1) 'Fill oTmpTable with contents of table to be sorted For Each oCell In SourceTable.Range.Cells With pTmpTable .Cell(i, 1).Range.Text = Left(oCell.Range.Text, _ Len(oCell.Range.Text) - 2) End With i = i - 1 Next 'Sort pTmpTable.Sort 'Redefine source table contents based on sort With SourceTable For i = 1 To .Range.Columns.Count For j = 1 To .Range.Rows.Count k = k + 1 Set oRng = pTmpTable.Cell(k, 1).Range .Cell(j, i).Range.Text = Left(oRng.Text, Len(oRng.Text) - 2) Next j Next i End With 'Clean up. pTmpDoc.Close SaveChanges:=False Set oRng = Nothing End Sub
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> Jezebel, > [quoted text clipped - 95 lines] >> Set oTmpTable = Nothing >> End Sub Greg Maxey - 08 Feb 2006 04:10 GMT Jezebel,
I played with your pCell suggestions some more and adapted the code to sort by users choice "top to bottom\left to right" or left to "right\top to bottom"
If I find more time I may add a userform to avoid the slightly ambiguous msgboxes. Thanks for the tips:
Option Explicit Dim pCell1 As Word.Cell Dim pCell2 As Word.Cell Sub TableSorter() Dim i As Long Dim j As Long Dim k As Long Dim pTmpDoc As Word.Document Dim pTmpTable As Table Dim oRng As Word.Range Dim SourceTable As Table Set SourceTable = Selection.Tables(1) i = SourceTable.Range.Cells.Count 'Insert a temporary 1 column/multi-row table in a temporary document Set pTmpDoc = Documents.Add(Visible:=False) Set pTmpTable = pTmpDoc.Tables.Add(Range:=pTmpDoc.Content, _ NumRows:=i, NumColumns:=1) 'Fill oTmpTable with contents of the table to be sorted TableFillAndRefill SourceTable, pTmpTable 'Sort pTmpTable.Sort 'Redefine source table contents based on sort If MsgBox("Do you want to sort left to right\top to bottom?", _ vbYesNo, "Sort Order") = vbYes Then TableFillAndRefill pTmpTable, SourceTable Else If MsgBox("The table will be sorted top to bottom\left to right", _ vbOKCancel, "Sort Order") = vbOK Then With SourceTable For i = 1 To .Range.Columns.Count For j = 1 To .Range.Rows.Count k = k + 1 Set oRng = pTmpTable.Cell(k, 1).Range .Cell(j, i).Range.Text = Left(oRng.Text, Len(oRng.Text) - 2) Next j Next i End With End If End If 'Clean up. pTmpDoc.Close SaveChanges:=False Set oRng = Nothing End Sub Sub TableFillAndRefill(pTable1 As Table, pTable2 As Table) Set pCell1 = pTable1.Cell(1, 1) Set pCell2 = pTable2.Cell(1, 1) Do pCell2.Range = Left$(pCell1.Range, Len(pCell1.Range) - 2) Set pCell1 = pCell1.Next Set pCell2 = pCell2.Next Loop Until pCell1 Is Nothing End Sub
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> Neat. > [quoted text clipped - 82 lines] > Set oTmpTable = Nothing > End Sub Jezebel - 08 Feb 2006 04:50 GMT Guess the next sophistication is to allow for merged and split cells in the source table. On that other hand, that might be the road to strabismus, insanity, and death.
> Jezebel, > [quoted text clipped - 144 lines] >> Set oTmpTable = Nothing >> End Sub Doug Robbins - Word MVP - 08 Feb 2006 05:08 GMT If the aim was to produce labels that are printed down then across rather than across then down the sheet,
' Macro to assign numbers to data source so that it can be sorted to cause labels to print down columns Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As Integer, k As Integer Message = "Enter the number of labels in a row" ' Set prompt. Title = "Labels per Row" ' Set title. Default = "3" ' Set default. ' Display message, title, and default value. labelcolumns = InputBox(Message, Title, Default) Message = "Enter the number of labels in a column" ' Set prompt. Title = "Labels per column" ' Set title. Default = "5" ' Set default. labelrows = InputBox(Message, Title, Default) ActiveDocument.Tables(1).Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1) ActiveDocument.Tables(1).Rows(1).Range.Cut k = 1 For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns For j = 1 To labelrows ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) * labelcolumns i = i + 1 Next j k = k + 1 i = i - 1 If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns * labelrows Next i ActiveDocument.Tables(1).Sort FieldNumber:="Column 1" ActiveDocument.Tables(1).Rows(1).Select Selection.Paste ActiveDocument.Tables(1).Columns(1).Delete
 Signature Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis.
Doug Robbins - Word MVP
I scrathed together the following today in response to a post in the tables group. I searched Google groups for a similiar solution and since I didn't see anything, I wanted to share it here. As alwasy constructive criticism is welcomed.
Objective is to sort a word table down and accross. Put cursor is table to be sorted and run the following.
Option Explicit Sub DownAndAccrossTableSorter() Dim i As Long Dim j As Long Dim k As Long Dim oCell As Cell Dim oTmpTable As Table Dim oRng As Word.Range i = Selection.Tables(1).Range.Cells.Count 'Insert a temporary 1 column/multi-row table at the end of the document With ActiveDocument.Paragraphs.Last .Range.Paragraphs.Add .Range.Tables.Add .Range, i, 1 End With 'Define this table Set oTmpTable = ActiveDocument.Tables(ActiveDocument.Range.Tables.Count) 'Fill oTmpTable with contents of table to be sorted For Each oCell In Selection.Tables(1).Range.Cells With oTmpTable .Cell(i, 1).Range.Text = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2) End With i = i - 1 Next 'Sort oTmpTable.Sort 'Redefine selected table contents based on sort With Selection.Tables(1) For i = 1 To .Range.Columns.Count For j = 1 To .Range.Rows.Count k = k + 1 Set oRng = oTmpTable.Cell(k, 1).Range .Cell(j, i).Range.Text = Left(oRng.Text, Len(oRng.Text) - 2) Next j Next i End With 'Clean up. oTmpTable.Delete Set oRng = Nothing Set oTmpTable = Nothing End Sub
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
Greg Maxey - 08 Feb 2006 10:01 GMT Doug,
Aim? There was no aim other than to kill a bit of that time shown slowly ticking down on the homepage ;-)
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> If the aim was to produce labels that are printed down then across rather > than across then down the sheet, [quoted text clipped - 82 lines] > Set oTmpTable = Nothing > End Sub Doug Robbins - Word MVP - 08 Feb 2006 21:27 GMT Just as well. Trying to take aim while wallowing is likely to not produce the desired results.
 Signature Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis.
Doug Robbins - Word MVP
> Doug, > [quoted text clipped - 88 lines] >> Set oTmpTable = Nothing >> End Sub Jezebel - 08 Feb 2006 22:04 GMT On the contrary. If you don't know where you're going, any path will get you there.
> Just as well. Trying to take aim while wallowing is likely to not produce > the desired results. [quoted text clipped - 91 lines] >>> Set oTmpTable = Nothing >>> End Sub Greg Maxey - 08 Feb 2006 22:28 GMT Jezebel,
Yep a simple Userform with two option buttons and a command button eliminated the message boxes. I will just file this away now as another seldom, if ever, used little gizmo. Thanks for the tips.
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> On the contrary. If you don't know where you're going, any path will get > you there. [quoted text clipped - 96 lines] >>>> Set oTmpTable = Nothing >>>> End Sub
|
|
|