Copy and paste Cells/Table from Excel to Word Template (and to the Correct Position)

Question

I have the following code:

Sub Excel2Word()
Dim BottomEquity As Range, BottomBond As Range
Dim WordApp As Word.Application
Set SB = Worksheets("SalesBrokerage")
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Open "C:\Customer\Templates\PIntern.dotx"
SB.Range("B3:G" & SB.Cells(SB.Rows.Count, 2).End(xlUp).Offset(1, 0).Row).Select
Selection.PasteExcelTable
HorizontalPosition = 10
VerticalPosition = 15
End Sub

I always get problems at Selection.PasteExcelTable and obviously the HorizontalPosition and VerticalPosition won't be true either.

The Word Template that I want to paste into looks like the following:

                       Our Overview
Equities 
Bonds

I want to do the equivalent of copying the selected range in my Excel document, opening the Word template, pasting the table between "equities" and "bonds" and then saving the document.

JosephC JosephC · Accepted Answer · 2018-09-20T13:27:28

No need to use selections, just copy, and paste to the appropriate objects.

Will search for the first Grave Character, and replace it with the table you copied. Also saved and closed the word document.

Sub Excel2Word()
    Dim BottomEquity As Range, BottomBond As Range
    Dim WordApp As New Word.Application

    Set SB = Worksheets("SalesBrokerage")
    'Set WordApp = CreateObject("Word.Application") ' You already have reference, this is not needed.
    WordApp.Documents.Open "C:\Customer\Templates\PIntern.dotx"
    WordApp.Visible = True                          ' Added for testing to see document

    'Copy / Paste
    SB.Range("B3:G" & SB.Cells(SB.Rows.Count, 2).End(xlUp).Offset(1, 0).Row).Copy

    Dim oFindPos As Long
    WordApp.Documents(1).Application.Selection.Find.Execute findtext:="`"
    oFindPos = WordApp.Documents(1).Application.Selection.Start

    WordApp.Documents(1).Range(oFindPos, oFindPos + 1).Paste

    'Save & Close Word Doc
    WordApp.Documents(1).Save
    WordApp.Documents(1).Close
    WordApp.Quit
End Sub

Copy and paste Cells/Table from Excel to Word Template (and to the Correct Position)

1 Answers