Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a MS Word 2007 document setup as a Mail Merge doc. I am using Excel as the data source. The MERGEFIELD ClientData contains an Excel file (test.xlsx).

I want to merge the data from the Excel file listed in ClientData into the respective Mail Merge document. However, whenever I start the Mail Merge the {MERGEFIELD ClientData} field gets resolved only once and does not select the next row from ClientData.

So this:

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\{MERGEFIELD ClientData}" \a \f 4 \h}

Becomes this after starting the merge:

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\test.xlsx" \a \f 4 \h}

So every Mail Merge doc uses the test.xlsx instead of the respective Excel document specific to the client (i.e test1.xlsx, test2.xlsx, test3.xlsx, etc.)

As the merge runs through each Mail Merge doc I expect to see this:

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\test.xlsx" \a \f 4 \h}

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\test1.xlsx" \a \f 4 \h}

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\test2.xlsx" \a \f 4 \h}

{LINK Excel.Sheet.12 "C:\\path\\to\\file\\test3.xlsx" \a \f 4 \h}

But for some reason this isn't happening. Does anyone have any suggestions?

Thanks!

share|improve this question

migrated from stackoverflow.com Dec 4 '12 at 22:23

This question came from our site for professional and enthusiast programmers.

    
Have you tried taking the merge field out of the LINK field into the document body and testing mail merge again to see if it works properly then? –  Adam Dec 5 '12 at 22:37
add comment

1 Answer

up vote 1 down vote accepted

The problem is that LINK fields always resolve all nested fields, sooner or later.

Your best bet is probably to use Word's Mailmerge events, then use the BeforeRecordMerge event to:

  • a. get the filename (test.xlsx etc.) (e.g. via Doc.Mailmerge.Datasource.Datafields("ClientData").Value)
  • b. either plug that back into the existing LINK field, or perhaps more easily, delete the existing LINK field and insert a new one.

Hint: insert a new Empty field, then insert the entire LINK field.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.