How to Copy an Excel Table to an Embeded Word document

Kaiser TCF

New Member
Joined
Jul 20, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hello gents,

I have an embedded word object inside my excel sheet and would like to copy a range of excel data as a table inside the word.

note that I already wrote a code that opens the word document and manipulates and copy data from excel to it ( NOT important: the code I wrote can paste excel data to word book mark and content control)

anyhow, I tried testing a similar code that copies the data from excel to a word document (not embedded) and it seems to work. but I just can't seem to do it in an embedded word document. see the code below. do I need to add a piece of code to fix this or do I need to start from scratch? (the red code is what gives the error)

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Dim sh As Shape
Dim objWord As Object
Dim objOLE As OLEObject
Dim wSystem As Worksheet
Dim cell As Range
Dim FileName As String



Set wSystem = Worksheets("sheet1")
Set sh = wSystem.Shapes("Object 3")
sh.OLEFormat.Activate
Set objOLE = sh.OLEFormat.Object

Set objWord = objOLE.Object
Workbooks("Table.xlsm").Worksheets("sheet1").Range("G2:J7").Copy
objWord.Selection.PasteExcelTable False, False, False
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,230
Office Version
  1. 365
Platform
  1. Windows
Try the following instead...

VBA Code:
objWord.Parent.Selection.PasteExcelTable False, False, False

Hope this helps!
 

Kaiser TCF

New Member
Joined
Jul 20, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Try the following instead...

VBA Code:
objWord.Parent.Selection.PasteExcelTable False, False, False

Hope this helps!
thank you! it works now!

but why do I need to add this "parent" in the code?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,230
Office Version
  1. 365
Platform
  1. Windows
Selection is a property of the Application object, not the Document object. So since objWord refers to the Word document, we can refer to the application with objWord.Parent.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,316
Messages
5,836,600
Members
430,441
Latest member
SurendraTantia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top