Hi BeJay,
I have a multiple worksheet Excel workbook. I need to embed
specific sheets from the workbook into a Word mailmerge letter. Can
you specify in Word which worksheet you want to embed or is it always
the first worksheet?
Ultimately I need to be able to show a different worksheet depending
on the address that is being merged. I'm struggling to think of how
this might be achieved - but any help on either or both queries would
be very much appreciated.
Depending on the version of Word, this can work more or less smoothly.
It goes like this:
Word uses LINK fields to bring in Excel worksheets. Look the field code
up in the Help to understand all the "bits and pieces".
In order to see it in action, copy a range of text in Excel, then use
Edit/Paste Special in Word to paste with a link. To make things simple
for the moment, choose the HTML format (probably the default). Now
press Alt+F9 to view the field code. Note how you get (among other
things) the full path to the workbook, then something like
"Sheet1!A1:C3"
When doing this with mail merge you can substitute mail merge fields
contaning the entire file path, or parts of the Range designation.
There are two tricky parts involved
1. Some versions of Word have a nasty tendency to convert the merge
field to plain text as soon as you toggle the field codes back to the
result. If this is the case on your machine, you have to make the merge
field substitution as the last step, then immediately execute the merge
to a new document
2. You can only edit the field codes when the object they represent is
"in line with the text". If you want an Excel table object, Word will
handle it like a graphic, which means it could be formatted with
text-wrap. And depending on the version of Word, copy/pasting over
Paste Special will default to text-wrap (or you may have it set as the
default for inserting pictures). That means you'll need to remove the
text wrap
Related to (2): the \p switch is what tells Word to create an OLE
object (graphic) instead of a Word table. While you can go from a Word
table (\r or \h switch) to an object, it's not possible to switch from
an object back to a Word table. This isn't tragic, but you need to be
aware of the limitation.
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org
This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail
