How to? Embedded Word (e-mail merge) Doc with tie to table in main Spreadsheet (.XLS)

[bilabong!]

Anyone with an Idea on how to make this work?

I've been using a mail merge for several different types of request
replies and have been copying the contact data from the main .XLS to a
local reusable .XLS and open up the needed reply which links to the
local spreadsheet. Doing real well with the process for over a year
now but need a way to share the process with others when I'm away.

I keep the main .XLS I work with on a shared e-room that my peers use
and want to set it up so that they can use the standard replies without
having to set up the mail merge on each of their machines and ensure
that any changes to the standard replies, as necessary, are updated in
one place.

I've embedded the Word docs within separate tabs of the main .XLS and
want to link the mail-merge information back to another tab within the
same main .XLS so that when they open up the main .XLS , insert the
requests (from another queue) and then copy the appropriate rows into
the "Response Table" tab, then open up the embedded Word Doc (same main
..XLS, different tab, for ease of use) and have the data from the
"Response Table" tab merge into the document.

I realize I will probably need to rebuild my external documents but
setting up the source link is where I run into trouble (actually, I
don't know where to link it to start adding the fields)

Thanks for taking the time if you have answers.

BK

 

[Peter Jamieson]

I suspect problem one is quite simply "an embedded Word document can't be a
mail merge main document" - if, for example, you enable the Mail Merge
toolbar in Word with an embedded document open, the first two icons are
greyed out.

I've never attempted what you're attempting so cannot be sure that there is
no workaround, but I'd already be considering what the alternatives might
be - i.e., probably either a separate Word document, or some way for the
user to open the embedded object as a separate document, perhaps using an
AutoOpen macro to try to work out the name of the Excel document the Word
document had been embedded in and connect to it.

But I have to say that even that approach seems likely to be difficult.
Apart from anything else, if the Excel sheet is already open, the only
connection method likely to work is the old DDE method, and even using that
it may not be possible to connect to the sheet you want. Feasibility will
also depend on whether you need to open your data source directly from your
"shared e-room" - if, for example, your Excel sheet can only be located via
an HTTP: or FTP: URL, you won't be able to use it as a Word mailmerge data
source.

Sorry to be the bearer of bad news, but I suspect this is going to be a lot
more difficult than you had hoped. However, if your users will not have
problems with downloading .doc and .xls files and storing them locally then
what is probably required is a more step-by-step approach - download this
file and put it here; open that file; click this button; and so on.

Peter Jamieson

 

[bilabong!]

Peter,
Sounds like you saved me a lot of wasted time trying to do something
that likely cannot be done. But you've given me some ideas to play
with. At the very least, nothing is really at stake, it's mostly an
intellectual exercise on my part; just trying to push the limits.

Thanks,
Bill