[BeJay]

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.

[Doug Robbins - Word MVP]

What you probably need to do is have the path\filename of the files that you
want included in a field in the data source and then
use a method similar to that in the "Graphics from data base" item under the
"Special merges" section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"BeJay" wrote in message
oups.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.

[Peter Jamieson]

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. using F9), then
merge to a new document, you should end up with the links you need. You may
need to select them all and press F9 to 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 to enable the 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 pressing F9.
c. Use Alt-F9 to 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
oups.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.

[BeJay]

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. using F9), then
merge to a new document, you should end up with the links you need. You may
need to select them all and press F9 to 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 to enable the 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 pressing F9.
c. Use Alt-F9 to 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

oups.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 at
http://cornell.veplan.net/article.aspx?&a=3815 it seems to have come
together like a plan! Thanks soooooo much for advice!

[BeJay]

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=3815 it seems 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?

[Peter Jamieson]

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
ups.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=3815 it seems 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?

[BeJay]

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

ups.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=3815it seems 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!!

[Peter Jamieson]

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

ups.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=3815it seems 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!!

[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

[Peter Jamieson]

Hi BeJay,

Let's continue if necessary in the other conversation.

Peter Jamieson
"BeJay" wrote in message
oups.com...
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