none
Extract all fields/values populated in mail merge (Word) to excel/access RRS feed

  • Question

  • Hello,

    I was wondering if it was possible to extract all fields and/or values populated in a finalized Word mail merge document to excel or acess.

    I was looking to accomplish the following:

    1. exporting all merge fields in a letter template to quickly able to audit/validate fields used in multiple letter templates (and it would serve the purpose for auditing the letter templates)

    2. export all values populated in the merge fields to be able to audit/validate values ran in the mail merge. So for example, having a macro that would take a 500 page mail merge, each letter has 30 merge fields, creating a new record for each record merged onto the letter. I would then take this excel or access file, compare it to my original data, and able to build a control/audit to validate no records were excluded/changed.


    Ana Quevedo
    Thursday, January 27, 2011 7:19 PM

Answers

  • Once a merge is executed, the mergefields ARE replaced by the data that is contained in the corresponding field in the data source.

    Therefore, it is NOT possible to do what you are seeking and I question why you want to do it.

    The only way you could do something like that would be to use code to replace the determine the data source that is attached to the mail merge main document, convert that document to a normal Word Document and then convert the MergeFields in the document to docvariable fields and the activate the datasource and iterate through the records, setting the values of variables in what was the Mail Merge main document to the values from the fields in the datasource and update the fields in the document and save it before going on to the next record.

    For other reasons, this method is used in the add-in that you can download from the following page of fellow MVP Graham Mayor's website

    http://www.gmayor.com/ManyToOne.htm

    But, I stress again, I do not think that any validation process that you come up with would be any better than either of the original methods of creating the document.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Anamque" wrote in message news:0db43a67-9555-46c4-9b56-09201a61fae9@communitybridge.codeplex.com...

    Hello,

    I was wondering if it was possible to extract all fields and/or values populated in a finalized Word mail merge document to excel or acess.

    I was looking to accomplish the following:

    1. exporting all merge fields in a letter template to quickly able to audit/validate fields used in multiple letter templates (and it would serve the purpose for auditing the letter templates)

    2. export all values populated in the merge fields to be able to audit/validate values ran in the mail merge. So for example, having a macro that would take a 500 page mail merge, each letter has 30 merge fields, creating a new record for each record merged onto the letter. I would then take this excel or access file, compare it to my original data, and able to build a control/audit to validate no records were excluded/changed.


    Ana Quevedo


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:13 AM
    Thursday, January 27, 2011 9:02 PM
  • Hi Ana,

    For auditing purposes, you could use a catalog/directory merge with the same fields as you use for the letter merge. Simply insert a double quote, comma, double quote between each field. After executing the merge, you can save the output as a plain text file - with a csv extension. Excel can open csv files. you can then compare the inputs against the outputs.

    A direct auditing of a mailmerge letter-type document is impractical unless you propose to use a series of Find/Replace operations to delete all the boilerplate text, then reformat what's left.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:13 AM
    Friday, January 28, 2011 10:53 AM

All replies

  • Once a merge is executed, the mergefields ARE replaced by the data that is contained in the corresponding field in the data source.

    Therefore, it is NOT possible to do what you are seeking and I question why you want to do it.

    The only way you could do something like that would be to use code to replace the determine the data source that is attached to the mail merge main document, convert that document to a normal Word Document and then convert the MergeFields in the document to docvariable fields and the activate the datasource and iterate through the records, setting the values of variables in what was the Mail Merge main document to the values from the fields in the datasource and update the fields in the document and save it before going on to the next record.

    For other reasons, this method is used in the add-in that you can download from the following page of fellow MVP Graham Mayor's website

    http://www.gmayor.com/ManyToOne.htm

    But, I stress again, I do not think that any validation process that you come up with would be any better than either of the original methods of creating the document.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Anamque" wrote in message news:0db43a67-9555-46c4-9b56-09201a61fae9@communitybridge.codeplex.com...

    Hello,

    I was wondering if it was possible to extract all fields and/or values populated in a finalized Word mail merge document to excel or acess.

    I was looking to accomplish the following:

    1. exporting all merge fields in a letter template to quickly able to audit/validate fields used in multiple letter templates (and it would serve the purpose for auditing the letter templates)

    2. export all values populated in the merge fields to be able to audit/validate values ran in the mail merge. So for example, having a macro that would take a 500 page mail merge, each letter has 30 merge fields, creating a new record for each record merged onto the letter. I would then take this excel or access file, compare it to my original data, and able to build a control/audit to validate no records were excluded/changed.


    Ana Quevedo


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:13 AM
    Thursday, January 27, 2011 9:02 PM
  • Hi Ana,

    For auditing purposes, you could use a catalog/directory merge with the same fields as you use for the letter merge. Simply insert a double quote, comma, double quote between each field. After executing the merge, you can save the output as a plain text file - with a csv extension. Excel can open csv files. you can then compare the inputs against the outputs.

    A direct auditing of a mailmerge letter-type document is impractical unless you propose to use a series of Find/Replace operations to delete all the boilerplate text, then reformat what's left.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:13 AM
    Friday, January 28, 2011 10:53 AM