Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adjusting table properties with VBA

    Hi, thanks for looking I hope you can help
    I have written code in an excel doc to create a table in new Word doc. My excel code opens word and my table is alive and well in word. It does however need some adjustment to the paragraph settings.

    The code for Word is different to Excel, at least I think it is. I have tried recording in word but I cant SELECT the table which is my main objective. How do I tell my new Word doc that I want to select all the rows and columns in my table and adjust the layout, paragaph etc, and then deselect the table when its done.

    I have tried referring to the table as Tables(1) but dont seem o be having a lot of success

    I'm using office 2010

    Code:
    Sub printer()
    '
    Const strRangeToCopy As String = "print_area"
    
        Dim appWord As Object
    
        Range(strRangeToCopy).Copy
    
        On Error Resume Next
            Set appWord = GetObject(, "Word.Application")
        On Error GoTo 0
        If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")
    
        With appWord
            .Documents.Add
            .Selection.Paste
            .Visible = True
                End With
     With ActiveDocument.Styles(wdStyleNormal).Font
         If .NameFarEast = .NameAscii Then
                .NameAscii = ""
            End If
            .NameFarEast = ""
        End With
        With ActiveDocument.PageSetup
            .LineNumbering.Active = False
            .Orientation = wdOrientPortrait
            .TopMargin = CentimetersToPoints(0.8)
            .BottomMargin = CentimetersToPoints(1)
            .LeftMargin = CentimetersToPoints(1.7)
            .RightMargin = CentimetersToPoints(0.1)
            .Gutter = CentimetersToPoints(0)
            .HeaderDistance = CentimetersToPoints(0.04)
            .FooterDistance = CentimetersToPoints(0.04)
            .PageWidth = CentimetersToPoints(21)
            .PageHeight = CentimetersToPoints(29.7)
            .FirstPageTray = wdPrinterDefaultBin
            .OtherPagesTray = wdPrinterDefaultBin
            .SectionStart = wdSectionNewPage
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .VerticalAlignment = wdAlignVerticalTop
            .SuppressEndnotes = False
            .MirrorMargins = False
            .TwoPagesOnOne = False
            .BookFoldPrinting = False
            .BookFoldRevPrinting = False
            .BookFoldPrintingSheets = 1
            .GutterPos = wdGutterPosTop
            
           End With
    
    Selection.Tables(1).Select
        With Selection.ParagraphFormat
            .LeftIndent = CentimetersToPoints(0)
            .RightIndent = CentimetersToPoints(0)
            .SpaceBefore = 0
            .SpaceBeforeAuto = False
            .SpaceAfter = 0
            .SpaceAfterAuto = False
            .LineSpacingRule = wdLineSpaceSingle
            .Alignment = wdAlignParagraphLeft
            .WidowControl = True
            .KeepWithNext = False
            .KeepTogether = False
            .PageBreakBefore = False
            .NoLineNumber = False
            .Hyphenation = True
            .FirstLineIndent = CentimetersToPoints(0)
            .OutlineLevel = wdOutlineLevelBodyText
            .CharacterUnitLeftIndent = 0
            .CharacterUnitRightIndent = 0
            .CharacterUnitFirstLineIndent = 0
            .LineUnitBefore = 0
            .LineUnitAfter = 0
            .MirrorIndents = False
            .TextboxTightWrap = wdTightNone
        End With
            
        
    End Sub
    Last edited by wotme; 2012-07-15 at 11:36.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Try:
    Code:
    Sub printer()
    Const strRangeToCopy As String = "print_area"
    Dim appWord As Object
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If appWord Is Nothing Then Set appWord = CreateObject("Word.Application")
    Range(strRangeToCopy).Copy
    With appWord
      .Documents.Add
      .Visible = True
      With .ActiveDocument
        .Range.Paste
        With .Styles(wdStyleNormal).Font
          If .NameFarEast = .NameAscii Then .NameAscii = ""
          .NameFarEast = ""
        End With
        With .PageSetup
          .PageWidth = CentimetersToPoints(21)
          .PageHeight = CentimetersToPoints(29.7)
          .LineNumbering.Active = False
          .Orientation = wdOrientPortrait
          .TopMargin = CentimetersToPoints(0.8)
          .BottomMargin = CentimetersToPoints(1)
          .LeftMargin = CentimetersToPoints(1.7)
          .RightMargin = CentimetersToPoints(0.1)
          .Gutter = CentimetersToPoints(0)
          .HeaderDistance = CentimetersToPoints(0.04)
          .FooterDistance = CentimetersToPoints(0.04)
          .FirstPageTray = wdPrinterDefaultBin
          .OtherPagesTray = wdPrinterDefaultBin
          .SectionStart = wdSectionNewPage
          .OddAndEvenPagesHeaderFooter = False
          .DifferentFirstPageHeaderFooter = False
          .VerticalAlignment = wdAlignVerticalTop
          .SuppressEndnotes = False
          .MirrorMargins = False
          .TwoPagesOnOne = False
          .BookFoldPrinting = False
          .BookFoldRevPrinting = False
          .BookFoldPrintingSheets = 1
          .GutterPos = wdGutterPosTop
        End With
        With .Tables(1).Range.ParagraphFormat
          .LeftIndent = CentimetersToPoints(0)
          .RightIndent = CentimetersToPoints(0)
          .SpaceBefore = 0
          .SpaceBeforeAuto = False
          .SpaceAfter = 0
          .SpaceAfterAuto = False
          .LineSpacingRule = wdLineSpaceSingle
          .Alignment = wdAlignParagraphLeft
          .WidowControl = True
          .KeepWithNext = False
          .KeepTogether = False
          .PageBreakBefore = False
          .NoLineNumber = False
          .Hyphenation = True
          .FirstLineIndent = CentimetersToPoints(0)
          .OutlineLevel = wdOutlineLevelBodyText
          .CharacterUnitLeftIndent = 0
          .CharacterUnitRightIndent = 0
          .CharacterUnitFirstLineIndent = 0
          .LineUnitBefore = 0
          .LineUnitAfter = 0
        End With
      End With
    End With
    End Sub
    Note that my code doesn't select anything.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool Nearly there!

    Excellent, thank you. I now realise I need to make one more adjustment to the pasted table and that is to adjust the row height. Can you start me off with the lins I need to add to adust the rowheight

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    For a 2cm fixed row height, you could use something based on:
    Code:
        .Tables(1).Rows.HeightRule = wdRowHeightExactly
        .Tables(1).Rows.Height = CentimetersToPoints(2)
    If you want the row height to be a minimum of 2cm, omit the first line.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    spot on. Brilliant.

Posting Permissions

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