Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tweak table properties in VBA code

    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2006
    Location
    Maryland, USA
    Posts
    690
    Thanks
    17
    Thanked 66 Times in 56 Posts
    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

  3. #3
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    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

  5. #5
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.AllowBreakAcrossPage s = 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

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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
       
    .Rows.LeftIndent = CentimetersToPoints(-0.18) .PreferredWidthType = wdPreferredWidthPoints .PreferredWidth = CentimetersToPoints(15.9) .Rows.HeightRule = wdRowHeightAuto .Rows.Height = CentimetersToPoints(0) .Rows.AllowBreakAcrossPages = True .Cells.WordWrap = True .Cells.FitText = True
    End With Next aTbl
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  10. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. #11
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  12. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  13. #13
    New Lounger
    Join Date
    Feb 2012
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  14. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    The following line in the earlier code is the one that imports the style from the attached template.

    ActiveDocument.UpdateStyles
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  15. #15
    New Lounger
    Join Date
    Oct 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 <<which seems logical while it says selection.cells(1)>>:

    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
    Last edited by ailioiu; 2012-10-24 at 11:09. Reason: reorder phrases

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •