Variable worksheets to attach to doc as result merged data

[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/cindymeister/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

 

[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]

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










- 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]

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]

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







- 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]

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?







- 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