Format All Tables including those with merged cells

[clhare]

Is there a way via macro to format the spacing and borders of all tables in a document even if they have merged horizontal or merged vertical cells? I tried using the following code to format all the tables and it works fine if there are cells that are merged horizontally, but I get an error if there is a vertically merged cell in the table. If there isn't way to make this work on vertically merged cells, is it possible to skip the table that can't be formatted by the macro and put the table "numbers" that were skipped in a message box so I can easily find the ones I have to fix manually rather than erroring out?



Sub FormatAllTables()

Dim oTable As Table
For Each oTable In ActiveDocument.Tables
With oTable.Select
With Selection
.Tables(1).Select
.Borders(wdBorderTop).LineStyle = wdLineStyleNone
.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Borders(wdBorderHorizontal).LineStyle = wdLineStyleSingle
.Borders(wdBorderHorizontal).LineWidth = wdLineWidth075pt
.Borders(wdBorderHorizontal).Color = 5459789
.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Cells.VerticalAlignment = wdCellAlignVerticalTop
.Tables(1).Rows(1).Select
.Borders(wdBorderBottom).LineWidth = wdLineWidth075pt
.Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
.Borders(wdBorderHorizontal).LineWidth = wdLineWidth050pt
.Borders(wdBorderHorizontal).Color = 5459789
.Cells.VerticalAlignment = wdCellAlignVerticalBottom
.Rows.HeadingFormat = True
.Rows.AllowBreakAcrossPages = False
.Tables(1).Select
.Rows.AllowBreakAcrossPages = False
.Rows.HeightRule = wdRowHeightAuto
.Tables(1).AutoFitBehavior (wdAutoFitWindow)
.Tables(1).Select
With Selection.ParagraphFormat
.SpaceBefore = 2
.SpaceBeforeAuto = False
.SpaceAfter = 0
.SpaceAfterAuto = False
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
End With
End With
Next oTable

End Sub


Thanks!

[macropod]

The code you've posted won't even run. You've clearly messed it up, with selections & procedures that have nothing to do with the context in which you've posted them. You could get the code to run by changing:

With oTable.Select
With Selection

to:

With oTable
.Select
With Selection
But then it errors-out further down, at:
.Borders(wdBorderHorizontal).LineWidth = wdLineWidth050pt

Commenting-out that line lets the code run to completion, but I have no idea whether the result looks anything like you intend.

[Frosty]

You can't access individual items in the .Rows collection on a table with vertically merged cells. And you can't access individual items in the .Columns collection on a table with horizontally merged cells.

I think, if you're trying to deal with merged cells, then you probably need to separate out the stuff you can do to the whole table (whether it's the table, the .cells collection of the table, the .rows collection, or the .columns collection.

From there, once you start having code like .Rows(1) or .Columns(2) -- your code will error out with merged cells. The way to handle that is to error trap for it... and then avoid running that code. Something along the lines of functions which can tell you whether you're able to run particular code... something like the following should get you started, structurally...


Public Sub TableSample()
Dim oTable As Table
For Each oTable In ActiveDocument.Tables
With oTable.Range.Cells
'do stuff you can do on all the cells
End With
With oTable.Rows
'do stuff you can do on all the rows
End With
With oTable.Columns
'do stuff you can do on all the columns
End With
'access individual columns here, if it's okay
If fTableHasHorizonalMerges(oTable) = False Then
'stuff to do with the first column only
With oTable.Columns(1)
End With
End If
'access individual rows here, if it's okay
If fTableHasVerticalMerges(oTable) = False Then
'stuff with the first row only
With oTable.Rows(1)
End With
End If
Next
End Sub

Public Function fTableHasVerticalMerges(oTable As Table) As Boolean
On Error Resume Next
If oTable.Rows(1).Cells > 1 Then
If Err.Number = 0 Then
fTableHasVerticalMerges = False
Else
fTableHasVerticalMerges = True
End If
End If
End Function

Public Function fTableHasHorizonalMerges(oTable As Table) As Boolean
On Error Resume Next
If oTable.Columns(1).Cells > 1 Then
If Err.Number = 0 Then
fTableHasVerticalMerges = False
Else
fTableHasVerticalMerges = True
End If
End If
End Function

[macropod]

You can't access individual items in the .Rows collection on a table with vertically merged cells. And you can't access individual items in the .Columns collection on a table with horizontally merged cells.

I think, if you're trying to deal with merged cells, then you probably need to separate out the stuff you can do to the whole table (whether it's the table, the .cells collection of the table, the .rows collection, or the .columns collection.
You can, however work through the .Cells collection and use each cell's .RowIndex and/or .ColumnIndex to decide on what to do next. You can also use the .Uniform property to test for merged cells and varying column widths. Since it's quite common for a table to have a full-width header row a full-height left column (or something approximating these), this can be used to advantage. For example:

Sub Demo()
Dim Tbl As Table, TblRow As Row, TblCell As Cell
For Each Tbl In ActiveDocument.Tables
With Tbl
If .Uniform = True Then
'Table rows & columns are consistent
For Each TblRow In .Rows
Next
Else
'Table has unequal column widths and/or row heights"
For Each TblCell In .Range.Cells
With TblCell
Select Case .RowIndex
Case 1 'Top Row
Case Else
If .ColumnIndex = 1 Then
'Left Column
Else
'Some other column
End If
End Select
End With
Next
End If
End With
Next
End Sub

[Frosty]

Ah... well forget my functions then. That's old school. Check the table's .Uniform property to get basically the same thing. That's great-- didn't know about that, Paul!

The only trouble with .RowIndex and .ColumnIndex (apart from suspicions that I can't confirm in a couple of simple tests--hey, maybe Microsoft actually fixed this at some point) is that they seem to (but I still don't totally trust it) give you the "top left" value of the merged cells. Which may not necessarily be what you want to deal with (You may want to put a border on the right edge of the first column... but row 3 has the first two cells merged... so you'll have a floating vertical line on the right edge of that merged cell, rather than a "skipped" line. More code can handle, I think, but something to watch out for.

Paul -- do you know of a way to detect whether a particular cell is a merged cell?

[macropod]

The only trouble with .RowIndex and .ColumnIndex (apart from suspicions that I can't confirm in a couple of simple tests--hey, maybe Microsoft actually fixed this at some point) is that they seem to (but I still don't totally trust it) give you the "top left" value of the merged cells.
Correct.

You may want to put a border on the right edge of the first column... but row 3 has the first two cells merged... so you'll have a floating vertical line on the right edge of that merged cell, rather than a "skipped" line.
If you apply the border to the merged cell, the border will span the full height on any adjacent cells that don't extend above/below that height. If that's not going to work, some circumlocution will allow you to work out which of the adjacent cells to apply the border to instead.

do you know of a way to detect whether a particular cell is a merged cell?
Word doesn't provide a direct means for testing that. If I had to, the way I'd go about it entails a lot of circumlocution (there's that word again) - testing the row & column indices of the surrounding cells and supplementing this with tests, if necessary, as to whether they have the same or greater heights & widths, as appropriate, to the cell under consideration. I've never found it worth the effort of going through all that work and nailing down the finer points of the logic. Clearly, it helps if you know something about the table's structure beforehand.

[Frosty]

You can't check the height property, because it can be undefined (default table returns values of 999999, which I'm pretty sure is the "whatever height it needs to be to show the text." Width property can help, but definitely becomes tricky when you're iterating through the cells collection (which goes from top left to bottom right), so that would be a pretty nasty mess of code to determine even the simplest "what cells does this merged cell span" question -- not to mention if you got into the ugliness of actual nested tables (uggh).

"Clearly, it helps if you know something about the table's structure beforehand." Haha, no kidding.

Non-uniform tables are a real disaster to code for, unless they are non-uniform in a uniform way. In a previous life, I spent a lot of time coding for word tables (several word version ago), and tables (at least back then), also had a sense of "history" about them... so that it was entirely unreliable to be able to get information about a table which had been merged, then split again, then merged again... ultimately the descriptors of the table really broke down and the cell addresses of what looked like a uniform table didn't even work (.Cell(2,2) wouldn't be the cell at row 2 column 2.

This may have been (and probably has been) improved... but back then, Microsoft finally said after 6 months of working with one of their senior engineers: "yeah, there are bugs... but they're so deep in the engine that we can't fix them."

Back to the OP's question -- CLhare: maybe you should first try skipping the whole initial VBA approach, and see if Word's Table Styles could get you closer to what you want. There are a lot of things you can set up in a table style, and if you're looking for a way to create uniform formatting for a whole bunch of differently structured tables, you may get some mileage out of investigating a style-based approach and then using vba to apply the style.

[macropod]

You can't check the height property, because it can be undefined (default table returns values of 999999, which I'm pretty sure is the "whatever height it needs to be to show the text."
There are other ways but, as I said, it .... entails a lot of circumlocution. In this case you can use the relative vertical offset of the first character in the cell (or paragraph below), adjusted for 'before' paragraph spacing.

I recently did a fair bit of work for someone who wanted cells shaded differently according to whether their content spilled beyond the cell boundaries vertically, horizontally or both. A real challenge given the plethora of tables with different paragraph formats (including negative indents, & tabstops beyond the cell boundaries) & font sizes, etc. in them.