Solved: Right align Table column

[dansyoz]

Hi
I am creating a number of tables via VBA in newly created documents.
The table creates fine and is populated from a multidimensional array - the number of dimensions depends on the particular table.

Everything works except I can't find a way to right align the numeric values in its respective column.

Sample code for a 2 column table (column 1 needs to be left aligned) to create it.

With tblSummary
.Columns(1).SetWidth ColumnWidth:=CentimetersToPoints(6), RulerStyle:=wdAdjustNone
.Columns(2).SetWidth ColumnWidth:=CentimetersToPoints(3), RulerStyle:=wdAdjustNone
.Rows.Alignment = wdAlignRowCenter
.Rows.SpaceBetweenColumns = CentimetersToPoints(0.38)
.Rows(1).Range.Font.Bold = wdToggle
.Rows(1).Range.Font.Name = "Arial"
.Rows(1).Range.Font.Size = 9
.Rows(1).Range.Font.Color = wdColorDarkRed
.Cell(1, 1).Range.Text = "Result"
.Cell(1, 2).Range.Text = "Count"
For j = 1 To UBound(gResSummArray, 2)
' Result text - need to left align
.Cell(j + 1, 1).Range.Text = gResSummArray(1, j)
' Count of results - need to right align
.Cell(j + 1, 2).Range.Text = gResSummArray(2, j)
.Rows(j + 1).Range.Font.Bold = False
.Rows(j + 1).Range.Font.Name = "Arial"
.Rows(j + 1).Range.Font.Size = 8
.Rows(j + 1).Range.Font.Color = wdColorBlack
Next j
.Sort ExcludeHeader:=True, FieldNumber:="Column 2", _
SortFieldType:=wdSortFieldAlphanumeric, _
casesensitive:=False, _
SortOrder:=wdSortOrderDescending
End With 'tblsummary


There is a header row.

Question
How can I do this without selecting the table ie. I am trying to avoid the Select.
Thanks

[fumei]

1. You appear to be using a table object: With tblSummary

Therefore you do not need to use Select.

2. Use Styles.

There seems to be a little confusion (at least on my part). You have:
.Rows.Alignment = wdAlignRowCenter
Yet you state that column 1 is left-aligned, and column 2 is right-aligned.

In any case, if you use a Style, all of this can be handled with one instruction. Instead of:
.Rows(1).Range.Font.Bold = wdToggle
.Rows(1).Range.Font.Name = "Arial"
.Rows(1).Range.Font.Size = 9
.Rows(1).Range.Font.Color = wdColorDarkRed
you could use:

.Rows(1).Range.Style = "MyWhatever"

[dansyoz]

Fumei
thanks for the pointer on styles.
I originally ruled out this path long ago becaue of limitations on what I can do with the end users templates but I hadn't looked at doing them programmatically.
That will definitely cut out some code so although as yet this doesn't seem to fix my problem it has helped me relook at styles.

However, other than the ApplyStyleFirstColumn and ApplyStyleLastColumn properties which might be useful if these were the only columns I'm interested in - I can't see how you would set the style for say a 5 column table where you want different styles (particularly alignment) in different columns.

BTW sorry for the confusion - I want to set row formats as well - e.g. central alignment but that is possible through .Rows.Alignment - but there appears to be no equivalent for columns.

I can define multiple styles but not sure how you select a column as a range within a table to use that style as

Dim rngjunk As Range
Set rngjunk = tblResults.Columns(3)
rngjunk.ParagraphFormat.Alignment = wdAlignParagraphCenter

generates a type mismatch.

[fumei]

Correct. You can not make a range object of a column. However, you CAN still process actions against cells in a specific column, by using ColumnIndex. Unfortunately I can not upload a demo file at the moment, but...

Assumptions:

FIVE styles (one for each column). They can be anything in format - that is the whole point of Styles! Left align, right align, font, font color, paragraph spacing...whatever you want.

Sub TableDifferences()
Dim oCell As Cell
For Each oCell In ActiveDocument.Tables(1).Range.Cells
Select Case oCell.ColumnIndex
Case 1
oCell.Range.Style = "Col1"
Case 2
oCell.Range.Style = "Col2"
Case 3
oCell.Range.Style = "Col3"
Case 4
oCell.Range.Style = "Col4"
Case 5
oCell.Range.Style = "Col5"
End Select
Next
End Sub


So, For Each cell in the table, if its ColumnIndex = 1, make its Style "Col1" style, if its ColumnIndex = 2, make its Style "Col2"....etc. etc.

[geekgirlau]

To take this one step further (and assuming that you are using a style "Col1" in column 1, "Col2" in column 2 etc) ...


Sub TableDifferences()
Dim oCell As Cell

For Each oCell In ActiveDocument.Tables(1).Range.Cells
oCell.Range.Style = "Col" & oCell.ColumnIndex
Next
End Sub

[dansyoz]

:) Thanks Fumei and geekgirlau.
This does the trick although traversing every cell is a less than efficient approach.

Why Microsoft set up the table object design such that you can deal with rows simply but not columns is a little strange - Still we can only use what Bill and his buddies give us to play with.
Thanks for the help.

[fumei]

The reason is that VBA can not deal with merged cells. Merged cells obviously mess up columns as discrete entities. For example:

Cell(1,1) Cell(1,2) Cell(1,3)
Cell(2,1) Cell(2,2) - NO column 3 as this cell is merged
Cell(3,1) Cell(3,2) Cell(3,3)

What is column 3? VBA has literally no idea, and trying to action with VBA on this will generate an error. A row is a row is a row, whether it has 1 column, or 5. Therefore a "row" always remains a discrete entity.

A column may be a column...or it may not be.

Nice improvement geekgirlau.