21.4. Working with Tables
Many people need to work with tables in their Word documents, either creating them from scratch or manipulating existing tables. VBA uses the Table object to represent a table, and the Table objects are gathered together into the Tables collection. To work with tables, you use the Tables property to return the Tables collection for the Document, Range, or Selection object in question.
The Tables collection and the Table object contain the following collections and objects:
The Rows collection contains the rows in the table. Each row is represented by a Row object.
The Columns collection contains the columns in the table. Each column is represented by a Column object.
The Cell object provides access to a specified cell directly from the Table object. You can also reach the cells in the table by going through the row or column in which they reside.
The Range object provides access to ranges within the table.
The Borders collection contains all the borders for the table.
The Shading object contains all the shading for the table.
21.4.1. Creating a Table
To create a new table from scratch (rather than converting existing text to a table), use the Add method with the Tables collection. The Add method takes the following syntax for the Tables collection:
expression.Add(Range, NumRows, NumColumns, DefaultTableBehavior, AutoFitBehavior)
The arguments are as follows:
expression is a required expression that returns a Tables collection. Typically, you'll want to use the Tables collection for the appropriate document.
Range is a required argument supplying the range where you want to insert the table. If the range is a selection (rather than being collapsed), the table replaces the range.
NumRows is a required Long argument specifying the number of rows the table is to have.
NumColumns is a required Long argument specifying the number of columns the table is to have.
DefaultTableBehavior is an optional Variant argument specifying whether the table autofits its columns to their contents or to the window when you change the contents or the window width. Use wdWord9TableBehavior to have the table autofit its columns or wdWord8TableBehavior (the default) to have the columns retain their width.
AutoFitBehavior is an optional Variant argument specifying the AutoFit behavior for the table. This argument applies only when DefaultTableBehavior is wdWord9TableBehavior. Use wdAutoFitContent to resize the columns to their contents, wddAutoFitWindow to resize the columns to the window width, or wdAutoFitFixed to use a fixed column width.
For example, the following statement inserts a new, blank, non-autofitting table containing 10 rows and 5 columns at the current position of the insertion point in the active document:
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=10, _
NumColumns:=5, DefaultTableBehavior:=wdWord8TableBehavior21.4.2. Selecting a Table
To select a table, specify the Document, Range, or Selection object involved, and then identify the Table object and use the Select method. This method takes no arguments.
The following statement selects the first table in the active document:
ActiveDocument.Tables(1).Select
The following statements declare the variable tempTable and then select the first table in the document named Log.docm and assign its Range object to tempTable:
Dim tempTable
Documents("Log.docm").Tables(1).Select
Set tempTable = Selection.Tables(1).RangeThe following statement selects the second table in the range named tempRange:
tempRange.Tables(2).Select
This statement selects the first table in the current selection:
Selection.Tables(1).Select
21.4.3. Converting Text to a Table
To convert text to a table (as opposed to inserting a new table from scratch), use the ConvertToTable method with an appropriate Range or Selection object. The ConvertToTable method takes the following syntax:
expression.ConvertToTable(Separator, NumRows, NumColumns, InitialColumnWidth, Format, ApplyBorders, ApplyShading, ApplyFont, ApplyColor, ApplyHeadingRows, ApplyLastRow, ApplyFirstColumn, ApplyLastColumn, AutoFit, AutoFitBehavior, DefaultTableBehavior)
The arguments are as follows:
expression is a required argument specifying an expression that returns a Range object or a Selection object.
Separator is an optional Variant argument specifying the separator character (also known as the delimiter character) to use to mark where the column divisions were. You can use these values for Separator:
ConvertToTablewdSeparateByCommas separates column information at commas.
wdSeparateByDefaultListSeparator separates column information at the currently specified Other list separator character (the character shown in the text box alongside the Other option button in the Convert Table to Text dialog box).
wdSeparateByParagraphs separates column information at the paragraph marks.
wdSeparateByTabs (the default separator if you don't specify one) separates column information at tabs.
Alternatively, you can specify a single separator character of your choice as a string or between double quotation marks. For example, enter Separator: ="|" to use a vertical bar [|] as the separator.
NumRows is an optional Variant argument specifying the number of rows the table should have. If you omit the NumRows argument, Word decides the number of rows in the table based on the number of columns specified and/or the number of the chosen separator characters it finds.
NumColumns is an optional Variant argument specifying the number of columns the table should have. As with NumRows, if you omit the NumColumns argument, Word decides the number of columns in the table based on the number of rows specified and/or the number of the chosen separator characters it finds.
InitialColumnWidth is an optional Variant argument that you can use to specify the initial width (in points) of each column in the table. If you omit the InitialColumnWidth argument, Word uses the full width of the page — from margin to margin — and allocates an equal width to each column, regardless of the relative widths of the contents of the columns. The InitialColumnWidth argument is useful primarily for restraining tables from using the full width of the page automatically. In many cases, autofitting the columns provides a better solution.
Format is an optional Variant argument that you can use to specify one of Word's built-in autoformat styles for tables. To use the Format argument, specify the appropriate WdTableFormat constant (such as wdTableFormatElegant to specify the Elegant autoformat style). If you choose to apply a format, you can specify which properties of the autoformat style to apply to the table by using the following optional Variant arguments:
Set ApplyBorders to True to apply the border formatting, or to False not to apply it.
Set ApplyShading to True to apply the shading, or to False not to apply it.
Set ApplyFont to True to apply the font formatting, or to False not to apply it.
Set ApplyColor to True to apply the color formatting, or to False not to apply it.
Set ApplyHeadingRows to True to apply any heading-row formatting, or to False not to apply it.
Set ApplyLastRow to True to apply any last-row formatting, or to False not to apply it.
Set ApplyFirstColumn to True to apply any first-column formatting, or to False not to apply it.
Set ApplyLastColumn to True to apply any last-column formatting, or to False not to apply it.
AutoFit is an optional Variant argument you can set to True to have Word adjust the column width to best fit the contents of the cells. When autofitting, Word doesn't increase the overall width of the table — it either reduces the table or keeps it the same width.
AutoFitBehavior and DefaultTableBehavior are as described in the section "Creating a Table," earlier in the chapter.
The following statement converts the current selection to a five-column table, separating the information at commas. It applies autofitting to the table based on cell content and sets the cells to resize automatically:
Set myTable = Selection.ConvertToTable(wdSeparateByCommas, _
Selection.Paragraphs.Count, 5,,,,,,,,,,, True, _
wdAutoFitContent, wdWord9TableBehavior)21.4.4. Making Sure the Selection Is within a Table
Before running any procedure that is intended to manipulate a table, it's a good idea to make sure that the current selection actually is within a table. Use the wdWithInTable argument of the Information property for the selection. wdWithInTable is Boolean, returning True if the selection is in a table and False if it isn't. For example:
If Selection.Information(wdWithInTable) = True Then
'take actions here
End If21.4.5. Finding Out Where the Selection Is in the Table
In addition to establishing whether the selection is in a table, you can also use the Information property to find out other information that can be useful when working with tables via a Range object or Selection object.
Once you've established that the selection is within a table (probably by using the wdWithinTable argument), check whether the selection is at an end-of-row marker rather than being in a cell. If the selection is at an end-of-row marker, certain actions fail. For example, attempting to select the current cell or column fails because the selection is outside any cell or column, but attempting to select the current row succeeds.
To check whether the selection is at the end-of-row marker, use the AtEndOfRowMarker argument for the Information property. The following statement moves the selection left one character (into the last cell in the same row) if the selection is at the end-of-row marker:
If Selection.Information(wdAtEndOfRowMarker) = True Then _ Selection.MoveLeft Unit:=wdCharacter, Count:=1
If the selection contains the end-of-row marker, rather than being a collapsed selection (an insertion point) before the marker, the wdAtEndOfRowMarker argument returns False. To avoid a selected end-of-row marker causing problems in your procedures, collapse the selection if it isn't collapsed before checking whether it's at the end-of-row marker. The following statements do this, using a variable named curSel to restore the selection it collapses, unless collapsing the selection leaves the selection at an end-of-row marker:
Dim curSel
With Documents("Communications.docm")
If Selection.Type <> wdSelectionIP Then
Set curSel = Selection.RangeSelection.Collapse Direction:=wdCollapseStart
End If
If Selection.Information(wdAtEndOfRowMarker) = True Then
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdMove
Else
If curSel <> " " Then curSel.Select
Set curSel = Nothing
End If
End WithAfter establishing that the selection is safely in a table, you can retrieve six useful pieces of information about the table:
wdStartOfRangeColumnNumber returns the number of the column in which the beginning of the selection or range falls. The following statement selects the column in which the current selection begins:
Selection.Tables(1).Columns(Selection.Information _ (wdStartOfRangeColumnNumber)).Select
wdEndOfRangeColumnNumber returns the number of the column in which the end of the selection or range falls. The following statements delete the column in which the range testRange ends if the range is more than one column wide:
With testRange If .Information(wdStartOfRangeColumnNumber) <> _ .Information(wdEndOfRangeColumnNumber) Then _ .Tables(1).Columns(.Information _ (wdEndOfRangeColumnNumber)).Delete End With
wdStartOfRangeRowNumber returns the number of the row in which the beginning of the selection or range falls.
wdEndOfRangeRowNumber returns the number of the row in which the end of the selection or range falls.
wdMaximumNumberOfColumns returns the highest number of columns in any row in the selection or range.
wdMaximumNumberOfRows returns the highest number of rows in the specified selection or range in the table.
21.4.6. Sorting a Table
To sort a table, identify the table and use the Sort method. Sort takes the following syntax with the Table object:
expression.Sort(ExcludeHeader, FieldNumber, SortFieldType, SortOrder, FieldNumber2, SortFieldType2, SortOrder2, FieldNumber3,
SortFieldType3, SortOrder3, CaseSensitive, BidiSort, IgnoreThe, IgnoreKashida, IgnoreDiacritics, IgnoreHe, LanguageID)
The arguments are as follows:
expression is an expression that returns a Table object.
ExcludeHeader is an optional Variant argument that you can set to True to exclude the first row in the table (which is often the table header row) from the sort or to False to include the first row in the table.
FieldNumber, FieldNumber2, and FieldNumber3 are optional Variant arguments specifying the first, second, and third fields by which to sort (respectively). Usually you'll want to specify at least FieldNumber; if you don't, Word performs an alphanumeric sort on the table.
SortFieldType, SortFieldType2, and SortFieldType3 are optional Variant arguments specifying the type of sorting you want to use for FieldNumber, FieldNumber2, and FieldNumber3, respectively. For U.S. English, the options are alphanumeric sorting (wdSortFieldAlphanumeric, the default), numeric sorting (wdSortFieldNumeric), or date sorting (wdSortFieldDate).
SortOrder, SortOrder2, and SortOrder3 are optional Variant arguments specifying the sorting order for FieldNumber, FieldNumber2, and FieldNumber3. Use wdSortOrderAscending to specify an ascending sort (the default) or wdSortOrderDescending to specify a descending sort.
CaseSensitive is an optional Variant argument that you can set to True to specify case-sensitive sorting. The default setting is False.
The next five arguments (BidiSort, IgnoreThe, IgnoreDiacritics, IgnoreKashida, and IgnoreHe) are for specialized sorting (such as right-to-left languages, Arabic, and Hebrew).
LanguageID is an optional Variant argument that you can use to specify the language in which to sort. For example, to sort in Lithuanian, you could specify wdLithuanian for LanguageID. For sorting in your default language, you can omit this argument.
21.4.7. Adding a Column to a Table
To add a column to a table, use the Add method with the Columns collection for the appropriate Table object. The Add method takes the following syntax for the Columns collection:
expression.Add [BeforeColumn]
Here, expression is a required expression that returns a Columns collection, and BeforeColumn is an optional Variant argument specifying the column to the left of which you want to insert the new column.
The following statements use the Count property to check the number of columns in the first table in the active document and, if the table contains fewer than five columns, add one or more columns to bring the number of columns up to five. Each new column is added before the existing last column in the table:
With ActiveDocument.Tables(1)
.Select
If .Columns.Count < 5 Then
Do Until .Columns.Count = 5
.Columns.Add BeforeColumn:=.Columns(.Columns.Count)
Loop
End If
End With21.4.8. Deleting a Column from a Table
To delete a column, identify it and use the Delete method. Delete takes no arguments. The following statement deletes the first column in the table referenced by the object variable myTable:
myTable.Columns(1).Delete
21.4.9. Setting the Width of a Column
You can set the width of a column by using the AutoFit method, by using the SetWidth method, or by specifying the Width property for the column.
The AutoFit method resizes each column automatically to a width suitable to its contents. AutoFit takes no arguments. The following statement uses the AutoFit method to resize each column in the first table in the active document:
ActiveDocument.Tables(1).Columns.AutoFit
The SetWidth method allows you to set the width of one or more columns and specify how the other columns in the table should change as a result. The syntax for the SetWidth method is as follows:
expression.SetWidth ColumnWidth, RulerStyle
Here, expression is an expression that returns the Columns collection or Column object whose width you want to set. ColumnWidth is a required Single argument specifying the width of the column or columns, measured in points. RulerStyle is a required Long argument that specifies how Word should adjust the width of the columns:
The default value, wdAdjustNone, sets all the specified columns to the specified width, moving other columns to the left or right as necessary. This argument is analogous to Shift+dragging a column border when working interactively.
wdAdjustFirstColumn applies the specified width to the first specified column, adjusting only as many columns to the right of this column as necessary. For example, widening the first column in a table slightly causes Word to narrow the second column but leave the third and subsequent columns unchanged; widening the first column significantly causes Word to narrow the second and third columns, leaving the fourth and subsequent columns unchanged. This argument is analogous to dragging a column border when working interactively.
wdAdjustProportional applies the specified width to the first specified column, keeping the right edge of the table in its previous position and adjusting all nonspecified columns proportionally to accommodate the change.
wdAdjustSameWidth applies the specified width to the first specified column, keeping the right edge of the table in its previous position and adjusting all the other columns to an identical width to accommodate the change. This argument is analogous to Ctrl+dragging a column border when working interactively.
The following statement sets the width of the second column in the first table in the active document to 50 points, adjusting the columns to the right of the second column proportionally:
ActiveDocument.Tables(1).Columns(2).SetWidth ColumnWidth:=50, _
RulerStyle:=wdAdjustProportionalThe Width property lets you change the width of a column without worrying about the effect on the other columns. Specify the width you want in points — for example:
ActiveDocument.Tables(11).Columns(44).Width = 100
21.4.10. Selecting a Column
To select a column, use the Select method with the appropriate Column object. Select takes no arguments. The following statement selects the second column in the third table in the document named Originals.docm:
Documents("Originals.docm").Tables(3).Columns(2).Select21.4.11. Adding a Row to a Table
To add a row, use the Add method with the Rows collection for the table. The Add method takes the following syntax for the Rows collection:
expression.Add [BeforeRow]
Here, expression is a required expression that returns a Rows object, and BeforeRow is an optional Variant argument specifying the row before which you want to add the new row. If you omit BeforeRow, VBA adds the new row after the last existing row in the table.
The following statement adds a new first row to the table referenced by the object variable myTable:
myTable.Rows.Add BeforeRow:=1
21.4.12. Deleting a Row from a Table
To delete a row, use the Delete method with the appropriate Row object. The Delete method takes no arguments. The following statement deletes the first row in the table referenced by the object variable myTable:
myTable.Rows(1).Delete
21.4.13. Setting the Height of One or More Rows
You can set the height of rows by letting Word set the row height automatically, by using the SetHeight method to specify an exact height or a minimum height, or by setting the Height property of the row or rows directly.
To have Word set the height of a row automatically, set the row's HeightRule property to wdRowHeightAuto. Word then adjusts the height of the row to accommodate the cell with the tallest contents. The following statement sets the HeightRule property for the second row in the fourth table in the active document to wdRowHeightAuto:
ActiveDocument.Tables(4).Rows(2).HeightRule = wdRowHeightAuto
To specify an exact height or a minimum height for one or more rows, use the SetHeight method with the row or rows. The syntax for the SetHeight property is as follows:
expression.SetHeight RowHeight, [HeightRule]
Here, expression is an expression that returns a Row object or a Rows collection. HeightRule is a required Variant argument specifying the rule for setting the row height: use wdRowHeightAtLeast to specify a minimum height or wdRowHeightExactly to specify an exact height. (The third setting for HeightRule is wdRowHeightAuto, which specifies automatic row height and which you won't want to use in this case.)
Instead of using the SetHeight method, you can set the Height property of the row or rows in question by specifying the height in points:
Documents("Tables.docm").Tables(3).Rows(3).Height = 3321.4.14. Selecting a Row
To select a row, use the Select method for the appropriate Row object. The Select method takes no arguments. The following statement selects the last row in the last table in the document named Tables.docm:
Documents("Tables.docm").Tables(.Tables.Count).Rows.Last.Select21.4.15. Inserting a Cell
To insert a cell, use the Add method with the Cells collection. The Add method takes the following syntax for the Cells collection:
expression.Add [BeforeCell]
Here, expression is an expression that returns a Cells collection, and BeforeCell is an optional Variant argument that specifies the cell to the left of which the new cell should be inserted. (If you omit the BeforeCell argument, VBA adds a new row of cells to the end of the table if you're using the Cells collection of the Columns collection, or a new cell to the first row in the table if you're using the Cells collection of the Rows collection.)
The following statement inserts a cell before the second cell in the first row of the first table in the document named Tables.docm:
Documents("Tables.docm").Tables(1).Rows(1).Cells.Add _
BeforeCell:=Documents("Tables.docm").Tables(1).Rows(1).Cells(2)21.4.16. Returning the Text within a Cell
To return the contents of a cell, use the Text property of the Range object for the cell. The following statement returns the text in the first cell in the second row of the third table in the active document and assigns it to the variable strCellText:
strCellText = ActiveDocument.Tables(3).Rows(2).Cells(1).Range.Text
Because the Text property includes the end-of-cell marker (which takes up two characters), you'll usually want to strip off the last two characters when assigning the Text property to a string:
strCellText = ActiveDocument.Tables(3).Rows(2).Cells(1).Range.Text strCellText = Left(strCellText, Len(strCellText) - 2)
Through the Range object, you can work with any of the objects and collections it contains. For example, to work with the paragraphs in a cell, use the Paragraphs collection.
21.4.17. Entering Text in a Cell
To enter text in a cell, assign the text to the Text property of the Range object for the cell. The following statements enter text in the first three cells in the first row of the current selection:
With Selection.Tables(1).Rows(1)
.Cells(1).Range.Text = "Sample text in first cell."
.Cells(2).Range.Text = "Sample text in second cell."
.Cells(3).Range.Text = "Sample text in third cell."
End With21.4.18. Deleting Cells
To delete cells, use the Delete method with the appropriate Cell object or Cells collection. When you delete one or more cells, you must specify what happens to the rest of the table — whether the cells to the right of those you deleted move to the left or whether the cells below those you deleted move up.
The syntax for the Delete method for the Cells collection and the Cell object is as follows:
expression.Delete [ShiftCells]
Here, expression is an expression that returns a Cells collection or a Cell object. ShiftCells is an optional Variant argument that specifies how the cells below or to the right of the deleted cell or cells should move. Use these values:
wdDeleteCellsEntireColumn deletes the whole column the specified cell or cells is in.
wdDeleteCellsEntireRow deletes the whole row.
wdDeleteCellsShiftLeft moves cells across to the left to fill the gap.
wdDeleteCellsShiftUp moves cells up to fill the gap.
The following statement deletes the first cell in the first row of the first table in the active document and shifts the other cells in the first row to the left to fill the gap:
ActiveDocument.Tables(1).Rows(1).Cells(1).Delete _
ShiftCells:=wdDeleteCellsShiftLeftFor procedures that rely on the user to make a selection within a table, you may want to determine how many rows or columns are in the selection before deciding how to shift the cells. The following example checks the number of rows and columns in the selection. If the selection is only one cell, or if the selection is all in one column, the code deletes the cell or cells and moves the other cells in the row to the left. If the selection is multiple cells in one column, the code deletes the cells and moves the other cells in the column up. If the selection spans columns and rows, the code displays a message box asking the user to make a selection in only one row or only one column:
With Selection
If .Columns.Count > 1 And .Rows.Count > 1 Then
MsgBox "Please select cells in only one row " _
& "or only one column."
End
Else
If .Cells.Count > 1 Then
If .Columns.Count > 1 Then
.Cells.Delete ShiftCells:=wdDeleteCellsShiftUp
Else
.Cells.Delete ShiftCells:=wdDeleteCellsShiftLeft
End If
Else
.Cells.Delete ShiftCells:=wdDeleteCellsShiftLeft
End If
End If
End With21.4.19. Selecting a Range of Cells
To select a range of cells within a table, declare a Range variable, assign to it the cells you want to select, and then select the range. The following example declares the Range variable myCells, assigns to it the first four cells in the first table in the active document, and then selects the range:
Dim myCells As Range
With ActiveDocument
Set myCells = .Range(Start:=.Tables(1).Cell(1, 1).Range.Start, _
End:=.Tables(1).Cell(1, 4).Range.End)
myCells.Select
End With21.4.20. Converting a Table or Rows to Text
To convert a table, a row, or a number of rows to text, specify the table, row, or rows and use the ConvertToText method. The ConvertToText method takes the following syntax:
expression.ConvertTotext(Separator, Nested Tables)
Here, expression is a required expression that returns a Table object, a Row object, or a Rows collection. Separator is an optional Variant argument specifying the separator character (also known as the delimiter character) to use to mark where the column divisions were. The possible values are as follows:
ConvertToTablewdSeparateByCommas separates column information by commas.
wdSeparateByDefaultListSeparator separates column information by the currently specified Other list separator character (the character shown in the text box alongside the Other option button in the Convert Table to Text dialog box).
wdSeparateByParagraphs separates column information with paragraph marks.
wdSeparateByTabs (the default separator if you don't specify one) separates column information by tabs.
Alternatively, you can specify a separator character of your choice as a string or between double quotation marks. For example, enter Separator: ="|" to use a vertical bar [|] as the separator. (You can supply more than one separator character here, but Word uses only the first character.)
The following statement converts the first table in the current selection to text using an asterisk (*) as the separator character:
Selection.Tables(1).ConvertToText Separator:="*"
You can use the ConvertToText method with a Table object, a Row object, or a Rows collection. The following statement converts only the first row of the selected table to tab-delimited text:
Selection.Tables(1).Rows(1).ConvertToText Separator:=wdSeparateByTabs
If you need to continue working with the contents of the table once you've converted it, assign a range to the table as you convert it. You can then work with the Range object afterward to manipulate the information. For example, the following statements convert the first table in the document named Cleveland Report.docm to text separated by paragraphs and assign the range exTable to the converted information, and then copy the range, create a new document, and paste in the information:
Dim exTable As Range
Set exTable = Documents("Cleveland Report.docm").Tables(1). _
ConvertToText(Separator:=wdSeparateByParagraphs)
exTable.Copy
Documents.Add
Selection.PasteOften when you copy and paste information from a Web page, it's in a tabular format. If you paste such a table into Word, it usually doesn't look right, is too bulky, and can be difficult to edit or format. In other words, you want to remove the Web page table definitions, but leave the data in a usable format within Word.
The following macro does just that:
Sub Untable()
On Error Resume Next
Selection.Rows.ConvertToText Separator:=wdSeparateByCommas, NestedTables:= _
True
Selection.MoveDown Unit:=wdLine, Count:=1
If Err Then MsgBox "No table was detected, dude.?
End SubTo use this macro, click somewhere within the text to put the insertion cursor in the table, then execute the macro. You may need to execute this macro more than once to completely eliminate all the Web-based tabular formatting.