VBA excel nesting data from excel into a table in word (copying excel data into Word table)

Question

I'm trying to paste a table into word from excel with VBA Excel.

I'm pasting it into a cell in a single column table of 4 rows I created in Word. So it is essentially a nested table.

I keep getting,

Run-time error 4605: Method 'PasteAsNestedTable' of object Selection failed

I'm trying to use PastAsNestedTable because otherwise I get the Run-time error about cells not matching as it is trying to merge the two tables.

So I get it is saying PasteAsNestedTable isn't a method of selection but how do I get around this issue?

My updated code goes:

Dim wdApp As Word.Application
Dim wdDoc as Word.Document
Dim tabl1 as Table, tabl2 as Table
Set wdApp = new Word.Application
With wdApp
     .visible = True
     .Activate
     .Document.Add(location)

Set wdDoc=wdApp.ActiveDocument
With wdApp
   Charts("chart1").ChartArea.Copy
     .Selection.GoTo what:=-1,Name:="chart1"
     .selection.Paste
(Then add some more charts)

End With 

Sheets("Sheet1").Range("A1:F10").Copy
    Set wdDoc=wdApp.ActiveDocument
    wdDoc.Bookmarks("table").Range.PasteAsNestedTable

With wdApp
(Then repeat above pasting charts + tables)

`

If I made the Range a ListObjects could I somehow copy it in that way?

Have you read this? msdn.microsoft.com/en-us/library/office/ff837670.aspx — Tim Wilkinson
You code can't work since wdApp is a new Word Application with a totally empty document. There cannot be a Bookmark "tableplace". And .Selection.GoTo what:=-1 Name:="tableplace" lacks a comma between the parameters. So it will not compile. — Axel Richter
@AxelRichter, I've left out a line where I open a template. The template has a bookmark named "tablespace". I've used the .Selection.GoTo what:=-1 Name:="tableplace" format for copying charts to other bookmarks in the word doc and that works fine. — JoshD

LocEngineer LocEngineer · Accepted Answer · 2017-02-15T12:54:29

Don't use Selection. This here works for me (Word with correct document already opened):

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set wdApp = GetObject(, "Word.Application")
Sheets(1).Range("A1:F10").Copy
Set wdDoc = wdApp.ActiveDocument
wdDoc.Bookmarks("tableplace").Range.PasteAsNestedTable

You can of course replace GetObject(, "Word.Application") with your new Word.Application and set wdDoc as wdApp.Documents.Open(pathtoyourdoc). Then combine with my answer from your other thread, replace wdthere with wdDoc and you should be good to go.

Edit I have changed my code to reflect your current variables and bookmark names:

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim tabl1 As Table, tabl2 As Table

Set wdApp = New Word.Application

With wdApp
     .Visible = True
     .Activate
    Set wdDoc = .Documents.Open(Location)
End With

Charts("chart1").ChartArea.Copy
wdDoc.Bookmarks("chart1").Range.Paste

Sheets("Sheet1").Range("A1:F10").Copy
wdDoc.Bookmarks("table").Range.PasteAsNestedTable

'(Continue like this for other charts + tables)

Note:

Do not use Douments.Add, as this will add a new empty document based on a template. This will not have your bookmarks. Use .Open instead.
Close With blocks properly
Do not set the same object over and over again. Set it once and work with that object
Do not use Selection unless absolutely necessary. Not necessary in this case.

VBA excel nesting data from excel into a table in word (copying excel data into Word table)

2 Answers