Pasting from spreadsheet to embedded Word Doc

L

Legacy 96851

Guest
The thread title pretty much describes what I want to, but am unable to do. I have a blank Word .doc embedded in a worksheet, and I simply want a macro to copy a range of cells from the spreadsheet into the document. Thinking it was relatively simple, I tried recording, but Excel doesn't record anything that happens in the embedded document, so the recorded macro did nothing but copy the desired range.

Very grateful in advance for any help.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is an example:

Code:
Sub Test()
    Dim obj As Object
    Range("A1").Copy
    Set obj = ActiveSheet.OLEObjects(1).Object
    obj.Paragraphs(1).Range.Paste
End Sub
 
L

Legacy 96851

Guest
Edit: Actually, from what I can tell, that only pastes the first cell in the range. I might be missing something obvious, but until I see it, I'll mess with this a little.

Sorry, I've only just learned to code for Excel, so my Word skills are... non-existant.
 
Last edited by a moderator:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Edit: Actually, from what I can tell, that only pastes the first cell in the range. I might be missing something obvious, but until I see it, I'll mess with this a little.

Sorry, I've only just learned to code for Excel, so my Word skills are... non-existant.

For me this pasted 2 cells:

Code:
Sub Test()
    Dim obj As Object
    Range("A1:A2").Copy
    Set obj = ActiveSheet.OLEObjects(1).Object
    obj.Paragraphs(1).Range.Paste
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yourself

Can't you just change Range("A1") to the range you want?:)
 
L

Legacy 96851

Guest
Hmm, no clue what happened there. I was hoping you'd give me enough credit to guess I knew to change A1 to the appropriate range, but that wasn't enough. However, then it started giving me an error 1004, then I closed the worksheet, reopened it, tried again, and it works fine.

I have no idea, I assume it had something to do with active sheets or active objects or things.

Edit: I've tried it a few more times. It seems like it *might* be that it doesn't work when the Word .doc isn't empty, but that's not quite correct, because sometimes it does work when there's text there.

Edit2: Alright, so what it looks like is that if the .doc has plain text in it, the macro works fine and it gets overwritten. But if it already has a "spreadsheet" in it, i.e. if you run the macro twice in a row, the second time, only the first cell of the range gets copied. I assume this has something to do with the way Word handles spreadsheets, but for now, it's sufficient to simply clear the doc at the beginning of the macro.
 
Last edited by a moderator:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Works fine for me when run repeatedly.:eek:

Doesn't do much mind you, just overwrites the existing data with the, well existing data from Excel.:)
 
L

Legacy 96851

Guest
Trust me, I know it's a worthless macro. It certainly wasn't my idea, but you know how it is... users, :sigh:.

Anyway, I still haven't resolved this issue. If run multiple times, the macro still only overwrites the first cell, and I'm using exactly what was posted here (again, obviously with my range instead of A1). The other issue is that sometimes I receive a 1004 error that says "Unable to access Object property of Object Class" and highlights the line with .Object. I haven't been able to figure out when or why this happens, but it isn't frequent enough to bother me as much as the other issue at hand.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,385
Messages
5,595,869
Members
414,027
Latest member
zippyfrog

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