# Microsoft Word: Macro to format all tables in doc with same font and autofit to window



## FryGirl (Apr 1, 2016)

Could somebody point me in a direction for a macro that will format all tables within a word doc with the same attributes?

Same font, same font points, auto fit to window, and maybe even cell height...


----------



## Macropod (Apr 1, 2016)

That could be as simple as:

```
Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
  With Tbl
    .Rows.HeightRule = wdRowHeightExactly
    .Rows.Height = InchesToPoints(0.5)
    .Style = "Table Grid"
    .Range.Font.Reset
    .Range.Style = "Normal"
  End With
Next
Application.ScreenUpdating = True
End Sub
```
The above code will apply the 'Table Grid' Style, thus removing all shading, etc., set all row heights to 0.5in and apply Word's 'Normal' Style to the table content. Applying Style to the table content means it acquires all of the font & paragraph format attributes of that Style.


----------



## FryGirl (Apr 1, 2016)

Hi Paul,

This is indeed a great start.  Since this will be used by many different people, I'm going to stay away from styles for the time being.

This modification below works fine for our needs, but the one last piece I can't find the answer for.  I would like to align the first column (minus the header row) to Align Left Center?

What mod is needed to do that?


```
Sub Demo()
    Application.ScreenUpdating = False
    Dim Tbl As Table
    For Each Tbl In ActiveDocument.Tables
      With Tbl
        .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
        .AutoFitBehavior (wdAutoFitWindow)
        .Rows.HeightRule = wdRowHeightExactly
        .Rows.Height = InchesToPoints(0.2)
'        .Style = "Table Grid"
'        .Range.Font.Reset
        .Range.Font.Size = 10
'        .Range.Style = "Normal"
        With .Rows(1)
            .HeadingFormat = True
            .Shading.BackgroundPatternColor = -603930625
            .Range.Font.Bold = True
        End With
      End With
    Next
    Application.ScreenUpdating = True
End Sub
```


----------



## Macropod (Apr 1, 2016)

You could use:

```
Sub Demo()
    Application.ScreenUpdating = False
    Dim Tbl As Table, i As Long
    For Each Tbl In ActiveDocument.Tables
      With Tbl
        .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
        .AutoFitBehavior (wdAutoFitWindow)
        .Rows.HeightRule = wdRowHeightExactly
        .Rows.Height = InchesToPoints(0.2)
'        .Style = "Table Grid"
'        .Range.Font.Reset
        .Range.Font.Size = 10
'        .Range.Style = "Normal"
        With .Rows(1)
            .HeadingFormat = True
            .Shading.BackgroundPatternColor = -603930625
            .Range.Font.Bold = True
        End With
        For i = 2 To .Rows.Count
          .Cell(i, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
        Next
      End With
    Next
    Application.ScreenUpdating = True
End Sub
```
Note: If you're not going to work with paragraph Styles, you're going to have to do far more than just change the font size and paragraph alignments. On my system, for example, all the text in the test table disappears with your implementation because the paragraph 'space before' setting is 12pt. Then there's the question of which font is being used, whether the user has applied bold, italics, etc. to some of the content, all of which .Range.Font.Reset restores to the Style defaults. If you need full control, you macro should both define and apply the required Styles. Regardless, you should not be overriding Styles the way the present code does with hard formatting. Aside from increasing file sizes, it makes the document harder to maintain and more prone to corruption.


----------



## FryGirl (Apr 1, 2016)

Thank you again Paul.



> If you need full control, you macro should both define and apply the required Styles.



I considered this, but thought the user would have to define the style on their individual systems.

If possible, could you show me an example of defining a new style and applying it?


----------



## Macropod (Apr 1, 2016)

For that, you could use code like:

```
Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table, i As Long, TblSty, StrSty As String
TblSty = Array("TableHead", "TableBody", "TableColA")
With ActiveDocument
  For i = 1 To .Styles.Count
    StrSty = StrSty & "|" & .Styles(i).NameLocal & "|"
  Next
  For i = 0 To UBound(TblSty)
    If InStr(StrSty, "|" & TblSty(i) & "|") = 0 Then
      .Styles.Add Name:=TblSty(i)
    End If
    With .Styles(TblSty(i))
      .BaseStyle = wdStyleNormal
      With .Font
        .Name = "Arial"
        .Size = 10
        Select Case i
          Case 0: .Bold = True
          Case 1: .Bold = False
          Case 2: .Bold = True
        End Select
        .Italic = False
      End With
      With .ParagraphFormat
        .SpaceBefore = 0
        .SpaceAfter = 0
        .LeftIndent = 0
        .FirstLineIndent = 0
        .RightIndent = 0
        Select Case i
          Case 0: .Alignment = wdAlignParagraphCenter
          Case 1: .Alignment = wdAlignParagraphCenter
          Case 2: .Alignment = wdAlignParagraphLeft
        End Select
      End With
    End With
  Next
  For Each Tbl In .Tables
    With Tbl
      .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
      .AutoFitBehavior (wdAutoFitWindow)
      .Rows.HeightRule = wdRowHeightExactly
      .Rows.Height = InchesToPoints(0.2)
      .Range.Font.Reset
      .Range.Style = "TableBody"
      With .Rows(1)
        .HeadingFormat = True
        .Shading.BackgroundPatternColor = -603930625
        .Range.Style = "TableHead"
      End With
      For i = 2 To .Rows.Count
        .Cell(i, 1).Range.Style = "TableColA"
      Next
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
```
I've included code to check whether the Style already exists before trying to add it, so you can run the macro on the same document multiple times. All that will happen in such cases is that the Styles will be updated to reflect whatever the macro (re-)applies.


----------



## FryGirl (Apr 1, 2016)

Hi Paul,

I get a Run-time error '5941' The requested member of the collection does not exist.

The error does not produce a debug, only an OK and Help.

I stepped thru the code an the error comes up right after...


```
For i = 0 To UBound(TblSty)
```

I'm testing this from home and I have 2007, but at work 2010.


----------



## Macropod (Apr 1, 2016)

I've made a minor code edit. Try it now.


----------



## FryGirl (Apr 1, 2016)

That is absolute superb Paul.  I simply can't thank you enough.  I'll check it again at work on Monday, but after my test here, I don't see any reason why it won't work there.

I can think of one addition I might need, but based on all the help you've provided so far, I think I might be able to make this one myself.

Have a blessed day!


----------

