VBA code to transfer a Range to a Word doc
 

For years I have struggled to find some elegant VBA code to transfer a
Excel Range to a bookmarked location in a Word document. It sounds s
simple and an obvious piece of functionality but I have found that OL
presents problems when I have VBA code that formats the embede
spreadsheet or that the spreadsheet is part of larger Excel Applicatio
suite (as is my case), or where the source spreadsheet is needed i
many Word documents.

Rather than live data/tables from Excel, I'd prefer to simply copy th
range of the sheet and paste it to Word.

Some solutions I have tried include;
1) DDE pokes (but didn't really understand it fully, or the code
borrowed opened a new document every time it ran)

2) Copy and paste to the clipboard, which was possible from Excel usin
VBA but once I got to Activate Word to find the "Paste location" in th
Word document, I was stumped because I didn't understand the righ
commands in the Word VBA library or the Library wasn't available. (
can fix this for me but as I want to share the code I need a way t
make sure the Word Object Library is available.)

3) Paste Linking the range from Excel to Word (which is unwieldy) an
then breaking the links in Word before saving the document so as t
save system overhead and ensure that saved Word documents don't updat
the pasted Range.

In short, this is how I imagine the code should go.

From Excel
Select Workbook.Worksheet and This Range
Copy
Check if Word is Active
If not, Open with Document New from Template "My Template - wit
Bookmarks"
Else Activate Word - MyDocument with Bookmarks
Find Book_Mark_1
Paste Range from ClipBoard
Activate Excel
Select This workbook
Cancel Copy
(Plus any error trapping routines)

I'd be greatful for any guidance on this issue. :confused

--
Message posted from http://www.ExcelForum.com