Certificate merge

[Lyndie]

Hi! Need a little able assistance, please. I have a 4-
page Certificate to generate for a group of people. I
want to (I think) create it as a merge document and the
fields will be CompanyName, TableNumber. Every company
will use it's name, but there are only 8 different table
numbers. Each table number relates to an Excel sheet
(all 8 sheets in the same workbook). When I merge the
files, I want the appropriate Excel chart to be placed in
the Certificate (based on the TableNumber field). Hope
I've made my intention clear and would appreciate any
help you can give.

Lyndie

 

[Peter Jamieson]

How to do this depends on what Exactly is in each sheet. If it is a chart
that has already been generated, and there is nothing else in the sheet,
then you can consider using a nested LINK field such as the following:

{ LINK Excel.Chart.8 "C:\\mycharts\\mycharts.xls" "{ MERGEFIELD
TableNumber }" \a \p }

All the {} need to be the special field braces you can enter using ctrl-F9.

You can generate the right sort of link by selecting the chart worksheet in
Excel, Eidt|Copy, then in Word, Edit|Paste Special, select Picture and
Insert as Link. Then select the picture in Word, right-click, use Format
Object|Layout and select the In line with text option. Then use alt-F9 to
display the field codes.

{ MERGEFIELD Tablenumber needs to evaluate to the Sheet name containing the
Chart. So either put "Chart1" (or whatever the correct sheet name is for the
first Chart) in the TableNumber field, or put the sheet number in (1, 2, 3
etc. ) and use something like

{ LINK Excel.Chart.8 "C:\\mycharts\\mycharts.xls" "Chart{ MERGEFIELD
TableNumber }" \a \p }

If what you actually have is a workbook with a table of values on each
sheet, and you want to generate the charts on the fly when you merge, I
suggest you don't try that - create the charts first, then merge.

The above works OK in Word 2000, but you may find that you get the same
chart every time. If so,
a. ensure Word Tools|Options|Print|Update Fields and Update Links are
checked
b. and/or merge to a new document then select the whole document (e.g.
ctrl-A), then press F9 to re-execute all the fields.

You may find that in later versions of Word the LINK gets "fixed" earlier
than you expect, and that the above will not work. But try that first.