tweak table properties in VBA code

Topic

New Reply

Hi,

I am having difficulty in writing a VBA code to tweak the settings which would apply to the whole table in the word document (Word 2010).

Requirements:
Table -> Preferred width: 8″ in inches, Alignment Left: -0.2″, Textwrapping: None

Row -> Size: None, Options: Allow row to break across pages

Column -> Size: No Preferred width

Cell -> Size: None, Vertical alignment: Top, Option: same as the whole table & wrap text

Any help would be greatly appreciated.

Thank you

Reply | Quote

Replies

Did you try recording such a macro? In the past, I think I’ve been able to record all of these items in macros, though not all in the same one.

Pam

Reply | Quote

Thanks for your reply, Pam.

I actually did try to get the code by doing the macro recording but the problem is I could not get into the table properties. You know how we normally want to select the whole table by moving the mouse over the top left hand corner and there appear like a square shape. When you click on it, it would select the whole table for you and if you right click on the square thing, you get to choose table properties from the drop-down list. Well, it did not let me do that. Therefore, I could not get anything from the recording.

Thank you

Reply | Quote

Welcome to the Lounge!

As a starting point, if you want to select a table while recording a macro, in the Ribbon you can click on Table Tools > Layout tab > Select button > Select table.

Gary

Reply | Quote

Hi Gary,

Thank you for your reply.

I did try it as suggested by you and was able to find out the VB code that I was looking for.

However, I still run into some issues when running the code.

i = 1
While i <= TablesCount
ActiveDocument.Tables(i).Select
ActiveDocument.Tables(i).Rows.LeftIndent = CentimetersToPoints(-0.18)
ActiveDocument.Tables(i).PreferredWidthType = wdPreferredWidthPoints
ActiveDocument.Tables(i).PreferredWidth = CentimetersToPoints(15.9)
ActiveDocument.Tables(i).Rows.HeightRule = wdRowHeightAuto
ActiveDocument.Tables(i).Rows.Height = CentimetersToPoints(0)
ActiveDocument.Tables(i).Rows.AllowBreakAcrossPages = True

With Selection.Cells(i)
.WordWrap = True
.FitText = False
End With

i = i + 1
Wend

There are three tables in the document. When I run the macro, the selection.cells(i) line does not seem to work in any of the tables. I wonder if I could get some help.

Thank you

Reply | Quote

You have restricted the WordWrap to a single cell (i) rather than the whole table. This works on Cell 1 in the first table, Cell 2 in the second table etc

Try something along the lines of the following air code.

Code:

Dim aTbl as Table
For each aTbl in ActiveDocument.Tables
  With aTbl
   [LEFT][COLOR=#333333][FONT font=Arial].Rows.LeftIndent = CentimetersToPoints(-0.18)[/FONT][/COLOR][COLOR=#333333][FONT font=Arial]    .PreferredWidthType = wdPreferredWidthPoints[/FONT][/COLOR]
[COLOR=#333333][FONT font=Arial]    .PreferredWidth = CentimetersToPoints(15.9)[/FONT][/COLOR]
[COLOR=#333333][FONT font=Arial]    .Rows.HeightRule = wdRowHeightAuto[/FONT][/COLOR]
[COLOR=#333333][FONT font=Arial]    .Rows.Height = CentimetersToPoints(0)[/FONT][/COLOR]
[COLOR=#333333][FONT font=Arial]    .Rows.AllowBreakAcrossPages = True
[/FONT][/COLOR][COLOR=#333333][FONT font=Arial]    .Cells.WordWrap = True
[/FONT][/COLOR][COLOR=#333333][FONT font=Arial]    .Cells.[/FONT][/COLOR][COLOR=#333333][FONT font=Arial]FitText[/FONT][/COLOR][COLOR=#333333][FONT font=Arial] = True[/FONT][/COLOR][/LEFT]  End With
Next aTbl

Reply | Quote

Thanks Andrew for sharing your code.

However, it got some error when encountering the following line

.Cells.WordWrap = True

with the message “Method or data member not found”. Apparently, Cells is under Selection object, not Table.

Thank you

Reply | Quote

Are you really sure you need those last two lines? Anyway, the following update should resolve the impasse – I actually tested it this time.

Code:

Dim aTbl As Table, aCell As CellFor Each aTbl In ActiveDocument.Tables
  With aTbl
   .Rows.LeftIndent = CentimetersToPoints(-0.18)
   .PreferredWidthType = wdPreferredWidthPoints
    .PreferredWidth = CentimetersToPoints(15.9)
    .Rows.HeightRule = wdRowHeightAuto
    .Rows.Height = CentimetersToPoints(0)
    .Rows.AllowBreakAcrossPages = True
    .Range.Paragraphs.WordWrap = True
    For Each aCell In .Range.Cells
      aCell.FitText = True
    Next aCell
  End With
Next aTbl

Reply | Quote

Thanks Andrew for your reply.

It does not give me any errors now which is great. But I do not see anything changes with each of the cells in the three tables in the document there after running the code.

I am not sure if I need to select each of the cells for each tables.

When I manually do it, it works. How I do it is I tested it by selecting a couple of rows by moving my cursor to the left side of the rows of the table and click the mouse to select the whole rows (at this stage, the rows were highlighted). I then right-clicked it to get the table properties. In the table properties window, go to Cell tab and click Options button to check “same as whole table” under cell margin. And then click OK to apply it, it worked.

If I run the recording macro, the code I see is

Code:

    With Selection.Cells(1)
        .WordWrap = True
        .FitText = False
    End With

Thank you

Reply | Quote

Dlee

I don’t know what you are trying to achieve. I would hope that you don’t see these lines of code have any effect – it would make your font sizes inconsistent and hide content if these settings do what they imply.

I can’t think of a good reason to turn off WordWrap in the first place and so this is not a setting that I ever fiddle with (although I would want to if someone had set a cell to not WordWrap).

FitText sounds like it autoreduces the font and/or spacing to allow the text to fit in the cell. I would have thought that the RowHeight being set to Auto would make this setting pointless as well.

Reply | Quote

Thanks Andrew for your reply.

Honestly, what’s happening is that I have recently bought an iPad2 with pages app installed so that I could open my word document to edit or view from my iPad. Later on, just realized that pages for iPad version lacks many features that word application offers such as limited font types, unable to add bullet or numbering points inside tables and many more.

So when I transferred the word doc from my PC and open it on my iPad, it gave me warnings that it’s unable to support this and that features. Hence, I lost all the bullet points in the table and lost my chosen font type altogether. Since pages allows us to save the document as .doc, I then tested it by saving the document as .doc on my iPad and emailed it to myself.

On the PC, I could open the document but low and behold, there are a lot of things getting modified such as the font type which I already anticipated would change, the whole table got shifted to the right (not centered anymore), the margin of the content inside each of the cells in the table got modified as well, page breaks got added in automatically (which I was very surprised at first because according to the original word document created in word office, there was no page break at all). So there are lots of strange things being added to the document.

Then, I was trying to be smart by thinking that after the document be transferred back to PC from iPad, I may be able to get away with this by creating a macro that would first change the font type back to my chosen font (this one should be relatively easy), and another macro that would move the whole table back to the center alignment and fixes the cells, and macros that would remove the unwanted page breaks.

Sorry I did not realize this could get very complicated especially dealing with the table.

Thank you so much and sorry to have caused confusion.

Reply | Quote

Why don’t you try applying a table style to the tables instead? A table style includes alignment, margins and formatting attributes.

You can configure a table style the way you want them and that would make it easier to apply all the table settings in one go.

The trick will then be “how do I get my table style into the documents coming out of the iPad”. I would not bother trying to modify the iPad application since I don’t know what comes out of there. Create a template with the attributes you want and then create a macro (in Normal.dotm or an addin) to:
1. Attach your preferred template
2. Import the styles from the template
3. Change the table styles on each table
4. Optionally, remove local formatting from the content (so the styles have a chance to work)

The code for those tasks might look like this…

Code:

Sub temp()’ Macro created by Chrysalis Design
  Dim sTempPath As String, aTable As Table
  sTempPath = Options.DefaultFilePath(wdUserTemplatesPath)
  With ActiveDocument
    .UpdateStylesOnOpen = False
    .AttachedTemplate = sTempPath & “MyTemplate.dotm”
  End With
  ActiveDocument.UpdateStyles
  For Each aTable In ActiveDocument.Tables
    aTable.Style = “Table Grid”
  Next aTable


  ActiveDocument.Range.ParagraphFormat.Reset
  ActiveDocument.Range.Font.Reset
End Sub

Reply | Quote

That’s great, Andrew. I got it to work now. I did not know that you could actually attach a template to a new word document. Thanks heap.

However, the other thing I need your help with is the paragraph outside the tables in the word document. They are still in the font type I do not want – for example, they are currently in Times New Roman.

In the MyTemplate.dotm, I set Arial to Normal style for all the paragraph, and then saved the template. I wonder what is the code to copy the Normal style from the template to the word document.

Thank you so much for your assistance.

Reply | Quote

The following line in the earlier code is the one that imports the style from the attached template.

ActiveDocument.UpdateStyles

Reply | Quote

Hello everybody,

I am trying to do the following macro:

– select first table in a document
– set the cell padding to 0 (up) 0 (down) 0.1 (left) 0.1 (right)
– go to the next table

My code is showed below, and it works, but it takes a very long time (almost a minute for a table of 30 rows and 10 columns), you can actually see each cell being processed, while using the menu commands, all the table is formatted instantly.

Sub table_process()
‘
‘ table_process Macro
‘
‘
Dim tabel As Word.Table, mesaj, celula As Cell
For Each tabel In ActiveDocument.Tables
tabel.Select
Selection.Rows.HeightRule = wdRowHeightAuto
For Each celula In Selection.Cells
celula.TopPadding = CentimetersToPoints(0)
celula.BottomPadding = CentimetersToPoints(0)
celula.LeftPadding = CentimetersToPoints(0.1)
celula.RightPadding = CentimetersToPoints(0.1)
Next
Next
End Sub

I couldn’t find a way to set the cell padding – the way it is done by Table Properties -> Cell -> Margins (untick “same as whole table” and set each value)

Recording a macro with the abovementioned commands resuts in the code below. If I copy this and put it into my macro, it doesn’t work. Apparently it applies the formatting only on the first cell of the table <>:

Sub Macro1()
‘
‘ Macro1 Macro
‘
‘
Selection.Tables(1).Select
With Selection.Cells(1)
.TopPadding = CentimetersToPoints(0)
.BottomPadding = CentimetersToPoints(0)
.LeftPadding = CentimetersToPoints(0.1)
.RightPadding = CentimetersToPoints(0.1)
End With
End Sub

Is there a way to do this, other than “for … next” loop?

Thank you in advance.

Dan

Reply | Quote

Since you have hijacked a thread about table styles – why don’t you actually do this by modifying the table style?

Alternatively, since you want the same padding for every cell in every table, why not use the “same as whole table” setting and set the padding at the table level rather than trying to iterate every table and cell individually.

Reply | Quote

Thank you for the reply, Andrew.

I am sorry for the apparent “hijack” – the thread is called “tweak table properties in VBA code” – I thought is suitable to place the question here.

Back to the problem – I couldn’t find a way to do the “same as the whole table” approach in VBA. This is why I posted the question here, maybe someone can show me.

I will check the style approach.

Thank you again.

Dan

Reply | Quote

Hello again, here’s the code I tried:

Sub table_process()

Dim tabel As Word.Table, mesaj, stil_tabel As Style
Set stil_tabel = ActiveDocument.Styles.Add(“caca maca”, wdStyleTypeTable)

With stil_tabel.Table
.TopPadding = CentimetersToPoints(0)
.LeftPadding = CentimetersToPoints(0.1)
.RightPadding = CentimetersToPoints(0.1)
.BottomPadding = CentimetersToPoints(0)
End With

For Each tabel In ActiveDocument.Tables
tabel.Style = “caca maca”
Next
End Sub

It doesn’t work, I get the following error, in line 2 (set):

“This style name already exists or is reserved for a built-in style”

Can somebody show me what I am doing wrong?
Thank you

Dan

Reply | Quote

There is already a table style applied to each table. If you didn’t know this then there is a great possibility that all tables use the same style. If this is the case, then you could use the following macro

Code:

  Dim aTbl As Table
  Dim sSty As Style
  If ActiveDocument.Tables.Count > 0 Then
    Set sSty = ActiveDocument.Tables(1).Style
    With sSty
      .Table.TopPadding = 0
      .Table.BottomPadding = 0
      .Table.LeftPadding = CentimetersToPoints(0.1)
      .Table.RightPadding = CentimetersToPoints(0.1)
    End With
  End If
  For Each aTbl In ActiveDocument.Tables
    aTbl.Style = sSty
  Next aTbl

Reply | Quote