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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Yourself

Can't you just change Range("A1") to the range you want?:)
 
Upvote 0
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:
Upvote 0
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top