Centering Excel Table pasted into Word with VBA

[alx1056]

Hello,

I am new to VBA and I am trying to paste a table to Word from Excel using strictly VBA.

My only issue is that I cannot center the table. I also can't get it to backspace once to the table to make it look nicer.

Here is my code:

Option Explicit
Sub talkToWord()

    Dim wdApp As Word.Application
    Dim myCat As Integer
    myCat = InputBox("Enter your Category: ")
    
    Set wdApp = New Word.Application
    With wdApp
        .Visible = True
        .Activate
        .Documents.Add
        
        With .Selection
            .ParagraphFormat.Alignment = wdAlignParagraphCenter
            .BoldRun
            .Font.Underline = True
            .TypeText "FY 19 CAT " & myCat
            .BoldRun
            .Font.Underline = False
            .TypeParagraph
            .Font.Size = 11
            .TypeParagraph
            .ParagraphFormat.Alignment = wdAlignParagraphLeft
            .BoldRun
            .TypeText ("Grade Number:")
            .TypeParagraph
            .TypeText ("Config #:")
            .TypeParagraph
            .TypeText ("Grade Name:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "Dept:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "Class/Subclass:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "Season Code:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "TimeFrame:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "Grade Type:")
            .TypeParagraph
            .TypeText (Chr(9) & "-" & "Index Breakpoint Bands by Volume Grade:")
            .TypeParagraph
            .BoldRun
            .TypeParagraph
            .InsertParagraph
        End With
        .Selection.WholeStory
        .Selection.ParagraphFormat.LeftIndent = InchesToPoints(-0.7)
        .Selection.ParagraphFormat.SpaceAfter = 5
        .ActiveDocument.PageSetup.TopMargin = InchesToPoints(0.3)
        .Selection.EndKey Unit:=wdStory
        .Selection.InsertBreak Type:=6
        .Selection.TypeBackspace
        Worksheets("Sheet2").Range("A1", "B4").Copy
        wdApp.Selection.Paste
        wdApp.Selection.TypeBackspace
        Selection.ParagraphFormat.Alignment = wdAlignRowCenter
        
    End With
    
            
   
End Sub

Thank you!

 

[Macropod]

Try:

Sub TalkToWord()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdRng As Word.Range
Dim myCat As Long: myCat = InputBox("Enter your Category: ")
    
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc.Range
    .PageSetup.TopMargin = InchesToPoints(0.3)
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Font.Bold = True
    .Font.Underline = True
    .InsertAfter "FY 19 CAT " & myCat
    .InsertParagraphAfter
    Set wdRng = .Characters.Last
    With wdRng
      .ParagraphFormat.Alignment = wdAlignParagraphLeft
      .ParagraphFormat.LeftIndent = InchesToPoints(-0.7)
      .ParagraphFormat.SpaceAfter = 5
      .Font.Underline = False
      .Font.Size = 11
      .InsertParagraphAfter
      .InsertAfter ("Grade Number:")
      .InsertParagraphAfter
      .InsertAfter ("Config #:")
      .InsertParagraphAfter
      .InsertAfter ("Grade Name:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Dept:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Class/Subclass:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Season Code:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "TimeFrame:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Grade Type:")
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Index Breakpoint Bands by Volume Grade:")
      .InsertParagraphAfter
      .InsertParagraphAfter
      .Collapse wdCollapseEnd
      .InsertParagraphAfter
      .InsertBreak Type:=wdLineBreak
      .Collapse wdCollapseEnd
      Worksheets("Sheet2").Range("A1", "B4").Copy
      .Paste
      .Tables(1).Rows.Alignment = wdAlignRowCenter
    End With
  End With
End With
End Sub