[BeJay]

On 18 May, 12:22, "Peter Jamieson"
wrote:
Hi BeJay,

I will try to look at this on the correct platform(s) as soon as I can, but
right now it's unlikely to be for a couple of days. If you don't see a
reponse here by (say) end of Monday, can you please post a reminder?

Peter Jamieson"BeJay" wrote in message

ups.com...



On 17 May, 21:04, "Peter Jamieson"
wrote:
We're already at the boundary of what MS lets you do without turning to
VBA-type code.

If you use Alt-F9 to look at the field codes in your destination
document,
do you see the sheet names you want in the LINK fields, or the same sheet
name every time?

Probably also worth checking Word Tools|Options|Print!"Update fields" and
"Update links".

Peter Jamieson

"BeJay" wrote in message

roups.com...

On 16 May, 17:48, BeJay wrote:
On 16 May, 08:29, "Peter Jamieson"
wrote:

There's a problem trying to merge LINK fields, because if you
manually
update the LINK field or save the Word document, Word tends to
"flatten" the
file name and the "subset name" in the LINK field, removing any
fields
you
have in there and replacing them by their results. This certainly
happens in
recent version of Word.

For example, if you have something like

{ LINK Excel.Worksheet.8 "c:\\myxlfiels\\mywb.xls" "{ MERGEFIELD
sheetname }!R1:R32767" }

when you update the field or save the document, the chances are that
when
you re-open the document, { MERGEFIELD sheetname } will have been
permanenty
replaced by one of the sheet names. So merging it after that, you
always get
the same sheet.

If you set up your LINK, do not update the LINK field (e.g.
usingF9),
then
merge to a new document, you should end up with the links you need.
You
may
need to select them all and pressF9to update them.

If you want the sheet to appear as it does in Excel, that's probably
the
only reasonably simple approach that will work. If you just want the
data in
the sheet in a tabular format, you can use the { DATABASE } field
approach
instead. In that case, proceed as follows:
a. use View|Toolbars toenablethe database toolbar
b. use the Insert|Database button to insert the data from one of
the
worksheets , choosing the "Insert as field" option in the last step.
Make
sure the field will work a second time (sometimes they don't) by
selecting
it and pressingF9.
c. Use Alt-F9to look at the field. it should have recorded the
workbook
pathname, a long connection string (you can probably cut almost all
of
it
out), and an \s parameter containing a SQL statement like

SELECT * FROM `Sheet1$`

insert your mergefield { MERGEFIELD Locref } instead of "Sheet1", so
you
have

SELECT * FROM `{ MERGEFIELD Locref }$`

(in this case I'm assuming LocRef does not contain the $)

Then try merging to a new document.

(As usual, all the {} have to be the special field code braces you
can
insert using ctrl-F9).

You have little control over the layout of this table except using
the
built-in styles. If you need more control, you either have to
postprocess
the output or use VBA and Word Events to do it as you go along. the
\*Mergeformat switch looks like it may preserve existing layout, but
when
you get to a sheet with more rows, the additional rows are not
formatted the
way you want. (I guess if all your sheets have the same number of
rows,
it
would work).

Peter Jamieson

"BeJay" wrote in message

roups.com...

I have a mail merge document and need to attach a different
worksheet(or workbook if this is the only way) dependant on merged
data. I am re-posting this in the hope someone can help prevent
insanity setting in. My brain is now stuck in a loop and I'm
really
not getting anywhere.....

In essence:-

Where MERGEFIELD LocRef = BOB, link to worksheet BOB, then Where
MERGEFIELD LocRef= FRED, link to worksheet FRED.

However, what is happening is:- Where MERGEFIELD LocRef = FRED,
The
merged letter is still linking to BOB worksheet and doesn't seem
to
want to
pick up FRED. I have tried on seperate workbooks called BOB, FRED
etc, but I still get the same problem. Again, any advice would be
most gratefully appreciated.

PS thanks to Doug for your suggestions yesterday, but can't get
them
to work for me (possibly more to do with my inadequacies than your
suggested routes!?! been trying all day!! )... any further
suggestions? - thanks in advance.- Hide quoted text -

- Show quoted text -

Peter .... I was just about to throw in the towel, when I found that
using your technique of not updating the link until after the merge in
combination with a bookmarking and MERGESEQ method found
athttp://cornell.veplan.net/article.aspx?&a=3815itseems to have come
together like a plan! Thanks soooooo much for advice!- Hide quoted
text -

- Show quoted text -

HOWEVER !?!?!? - have today tried F9 to refresh merged links in merged
document on Word2002 - it does not work, doesn't even look like it is
thinking of working. On Word2000 it works fine and it is clear that
it is refreshing the links as a message tells you so. "Word is
updating the fields in this document". This doesn't happen in 2002.
Anyone got any clues? Peter?? Anyone?- Hide quoted text -

- Show quoted text -

Have switched on the print options for update link and update , but
this makes no difference. Also in the destination document, I cannot
see the fields when I alt F9. I know this works on 2000, but I cannot
get it to work here on 2002. I don't know on 2002 what field codes
are carrying through, and can't quite believe that it worked so
perfectly on 2000 but isn't now! Peter. I know I am taking up some
bit of your valuable time, and really really do appreciate your
expertise but again would be very pleased indeed to see another
response from you!!- Hide quoted text -

- Show quoted text -

Hi Peter, Any luck?!?! I have a feeling that I have hit a brick wall
with this one. Appreciate all your help though. Thnx. Bev