Excel links in Word mail merge don't update

[Don Barton]

Word Mail Merge Problem in Word, Access, Excel XP.

For several years our laboratory has created a lab test reference manual
using Word - Access - Excel. This has worked very well until we upgraded our
MS Office to XP. We did not have this problem in Office 95-2000.

Background

We enter our lab test data in Access. One of the fields in Access is a path
link to Excel data. We then mail merge the Access data into a Word mail merge
template we wrote. When this is done we perform a Ctrl-A (select all data),
then F9 to update all links. The resultant Word document has lab test data
from Access with appropriate reference ranges in chart form from Excel.

The problem.

In Word XP, if I load the mail merge template, then click on the View Merged
Data icon, I can view the merge data for that record. If that record has a
valid link to the Excel data, it will update the record to include the Excel
chart for that test after selecting a Ctrl-A, then F9. This works fine and I
know my Excel path link is written correctly.

If I move to any other record, by clicking on the record select buttons, the
correct data is populated into the fields for that record EXCEPT the Excel
chart for the initial record shows up on the other records. Not good.

If I perform a Merge to New Document at this time, the correct data is
populated from Access into all the new records, but the same Excel chart is
in every record whether it's supose to have one or not. Doing a Ctrl-A, F9
does not change the chart data.

If I close and reopen the template, and skip using the View Merge Data, and
go directly to Merge to New Document - all Access data pulls into the new
document correctly. If I do Ctrl-A, then F9, I will get Error! Not a valid
Link error.


The Word template that processes the Excel link is :

{IF {MERGEFIELD Path_to_Reference_Chart}<> "" "Reference Range: "}

{Link ExcelSheet "{MERGEFIELD Path_to_reference_chart}" \p}


While in View Merge Data and pressing Alt-F9 (show field codes), I can
toggle the above field code and the correct Excel chart is referenced.

The imported Excel link from Access looks like this:

\\Server\Folder1\subfolder2\Excelsheetname.xls

If this is a syntax issue, then I shouldn't be able to get the correct Excel
Chart to merge into Word when viewing a specific record using View Merged
Data (at least initially).

Bottom line is:

How do I get my links to update properly in Word?

Thanks,

Don

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?RG9uIEJhcnRvbg==?=,

Your chart data is coming in via a LINK field. In some versions of Word, as soon
as you update a LINK field it turns any fields within it (the Mergefield, in
this case) to static text. I used to get this problem in Word 95 (I think it
was), but it disappeared in Word 97 (or something like that; version numbers are
a little foggy, this far down the line).

Now, the behavior has come back in Word 2003. And no, I know of no way to
suppress it.

Word Mail Merge Problem in Word, Access, Excel XP.

For several years our laboratory has created a lab test reference manual
using Word - Access - Excel. This has worked very well until we upgraded our
MS Office to XP. We did not have this problem in Office 95-2000.

Background

We enter our lab test data in Access. One of the fields in Access is a path
link to Excel data. We then mail merge the Access data into a Word mail merge
template we wrote. When this is done we perform a Ctrl-A (select all data),
then F9 to update all links. The resultant Word document has lab test data
from Access with appropriate reference ranges in chart form from Excel.

The problem.

In Word XP, if I load the mail merge template, then click on the View Merged
Data icon, I can view the merge data for that record. If that record has a
valid link to the Excel data, it will update the record to include the Excel
chart for that test after selecting a Ctrl-A, then F9. This works fine and I
know my Excel path link is written correctly.

If I move to any other record, by clicking on the record select buttons, the
correct data is populated into the fields for that record EXCEPT the Excel
chart for the initial record shows up on the other records. Not good.

If I perform a Merge to New Document at this time, the correct data is
populated from Access into all the new records, but the same Excel chart is
in every record whether it's supose to have one or not. Doing a Ctrl-A, F9
does not change the chart data.

If I close and reopen the template, and skip using the View Merge Data, and
go directly to Merge to New Document - all Access data pulls into the new
document correctly. If I do Ctrl-A, then F9, I will get Error! Not a valid
Link error.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail