Word macro copying range from Excel and pasting to page #

Question

I am using a word macro to open an excel file and copy a range, and to paste that as a table in my word document. Everything works great except that it pastes onto page 1 and not page 6 (where I need it). The one option is to change the paragraph number until I find my way to page 6 but is there any other way (I tried paragraph number 243 and it posted in the middle of page 5). The paragraph number in WordCount doesn't correspond. Please assist and thanks in advance.

Sub CopyExcelPasteWord()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim tbl As Excel.Range
    Dim WordTable As Word.Table

    Set exWb = objExcel.Workbooks.Open("C:\Users\a222012\Desktop\Standard Bank\2017\FASS\CSS Project\Cash Shared Services\Fees_Contract.xlsm")
    Set tbl = exWb.Sheets("Device_Selection").Range("A5:G26")
    tbl.Copy
    Selection.GoTo wdGoToPage, wdGoToAbsolute, 6 'Go to page 6

    ThisDocument.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, _
      WordFormatting:=False, RTF:=False

    Set WordTable = ThisDocument.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitWindow)

    exWb.Close

    Set exWb = Nothing
End Sub

I have found a solution to my problem. Add bookmarks to the word document where you want to paste to. Then instead of: ThisDocument.Paragraphs(1).Range.PasteExcelTable just use ThisDocument.Bookmarks("BookMark_Name").Range.PasteExcelTable — KoderM16
Hi KoderM. It would help site administration as well as other users if you'd put your solution in an "Answer" then mark it as the Answer :-) — Cindy Meister

KoderM16 KoderM16 · Accepted Answer · 2017-02-13T12:07:52

The solution of the above is to make use of bookmarks in Word. Go to the line in Word were you want to copy the text/table or anything from Excel, and insert a bookmark (and give it a name).

Then use the following code to paste to that bookmark:

Sub CopyExcelPasteWord()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim tbl As Excel.Range
Dim WordTable As Word.Table

Set exWb = objExcel.Workbooks.Open("Place path to file here including file name and extension")
Set tbl = exWb.Sheets("SheetNameHere").Range("RangeHere")
tbl.Copy


ThisDocument.Bookmarks("EnterBookmarkNameHere").Range.PasteExcelTable LinkedToExcel:=False, _
WordFormatting:=False, RTF:=False

'This next part just sets the table to fit across the page. The number in the bracket refers to the table 1 in the document. Change it accordingly.

Set WordTable = ThisDocument.Tables(1)
WordTable.AutoFitBehavior (wdAutoFitWindow)

exWb.Close

Set exWb = Nothing
End Sub

Word macro copying range from Excel and pasting to page #

1 Answers