Word VBA Table Column and Data Formatting

[WBURKERT]

Could someone tell me the VBA syntax to make a column in a Word table right justified?
I am also trying to make the Column(3) to always display numbers with two decimals and a comma seperating thousands. I would really, really like to have the column format be like currency - dollar sign aligned left and number aligned right. If I can get the number displayed with two decimals then I might just add a column next to it with a dollar sign in it. Hey thanks in advance.

Bill

With Goods
.Range.Style = "greenbar"
.Columns(1).Width = InchesToPoints(1.25)
.Columns(2).Width = InchesToPoints(4)
.Columns(3).Width = InchesToPoints(0.77)
' .Columns(3).NumberFormat = "$ #,##0.00"
' .Columns(3).HorizontalAlignment = xlRight
.Rows(1).Range.Font.Bold = True
.Rows.Alignment = wdAlignRowCenter
End With

 

[PHV]

Did you try to macro-record the formatting in word ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

Yes I did turn on Marco recording. Interestingly when macro recording is turned on I can't select the column like I can when the macro recorder is not running. Normally you get a small down arrow when you get on the top of a column but when macro recorder is turned on it doesn't seem to transistion to the arrow or let you select the column.

When I am in a cell and do align right I get
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight

but then I can't seem to figure out how to use this with .Column(3) because it seems like there is no Alignment or Paragrapgh or Format part of the command/sytax.

 

[fumei]

Use a Style for Column(3).

This seems very odd to me. You are taking data from Excel 9which DOES format numbers very well) and putting it into Word, which does NOT format numbers well. Or at least not in the dedicated way Excel does. It does not really fully support Currency, nor two-digit decimal, except as formfield content. As text in a table cell, that is exactly what it is - text. NOT numbers.

That being said, you can still do it.

Sub TableCol3()
Dim aTable As Table
Dim aCell As Cell
Dim Cell_Text As String
Dim whatever

Set aTable = ActiveDocument.Tables(1)

For Each aCell In aTable.Columns(3).Cells
   Cell_Text = CellText(aCell.Range.Text)
   whatever = IsNumeric(Cell_Text)
   If whatever = True Then
      aCell.Range.Text = Format(Cell_Text, "##0.00")
   End If
   aCell.Range.ParagraphFormat.Alignment = _
         wdAlignParagraphRight
Next
End Sub

You do not state specifics, but the above would take:

34.6789 in a cell in column 3, and:

1. make it 34.68
2. right align the cell

BTW: the code above uses the CellText function - previously posted but not commented on - to get just the text from each cell. Otherwise you get the text AND the end-of-cell marker. And that would mean it would NEVER be considered numeric.

Gerry

 

[WBURKERT]

Gerry,

I get an error at For Each Cell . . . and I think it has something to do with

Set aTable = ActiveDocument.Tables(1)

because if I change (1) to (2) then it works for the first table but never any others. I created an If statement to check and see if the table might be seven columns wide and when a seven column table is found it halts rights away with For Each Cell and again I think it has something to do with the Set aTable command. I have no idea what the (1) means, or why (2) kinda works and if this should change with each table? My logic remains strong but the darn command structures are my weak link. This really seems like the last piece of my work but like every step it requires a little help. Thank-you.

 

[WBURKERT]

Set aTable = docDest.Tables(1)
For Each aCell In aTable.Columns(3).Cells
Cell_Text = CellText(aCell.Range.Text)
whatever = IsNumeric(Cell_Text)
If whatever = True Then
aCell.Range.Text = Format(Cell_Text, "##,##0.00")
End If
aCell.Range.ParagraphFormat.Alignment = wdAlignParagraphRight
Next

If ColCount = 7 Then
Set aTable = docDest.Tables(1)
For Each aCell In aTable.Columns(7).Cells
Cell_Text = CellText(aCell.Range.Text)
whatever = IsNumeric(Cell_Text)
If whatever = True Then
aCell.Range.Text = Format(Cell_Text, "##,##0.00")
End If
aCell.Range.ParagraphFormat.Alignment = wdAlignParagraphRight
Next
End If

 

[WBURKERT]

Fixed. A good night sleep fixes many problems

 

[SkipVought]

"Fixed" is not very descriptive.

It's like playing a scale, do re me fa so la ti..., without resolution: it is maddening!

So the STATUS is not as important as the PROCESS!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WBURKERT]

I already had

Set Goods = docDest.Tables.Add(docDest.Bookmarks("Sec" & s).Range, RowCount, ColCount)

So I changed aTable to

Set atable = Goods

Keep me honest, thanks.