• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

? How to use MS Word merge field results after merge is completed

I have a 3rd party app that brings over data to a word document and fills the merge fields that we selected from a list to help create our own letters.  What I need to do is take the information in that letter that has been filled into the merge fields and calculate a few numbers and then fill a table with the information.

So the question is how do I access the information in the merge fields in vba, so that I can fill other custom  fields into the letter.  So I need the letter to load first with the data, then execute the vba code after the word document has been loaded.

Hoping someone has had to do something like this.  Not sure what sub to call my sub from when the document is loaded, and how to access the fields.

thanks
0
mgmhicks
Asked:
mgmhicks
1 Solution
 
Rgonzo1971Commented:
Hi,

The event  for it is

MailMergeDataSourceLoad

see example

http://support.microsoft.com/kb/285333/en-us

Res = Doc.MailMerge.DataSource.DataFields("ClientNumber").Value


Regards
0
 
GrahamSkanCommented:
A third party application might be doing something different, but the text that has been placed in Result document during the Word Mail Merge operation is not especially distinguished from the fixed text from the Main document. That means that there is no simple way to pull the merge text from the result document.

Some workarounds - the best would depend on your exact situation

1. You could consider using a special Style for the merge fields and looking for that in the result document. Quite a lot of work to set up.
2. Compare the Main and the Result documents. Not particularly easy.
3. Requery the datasource separately just before or after the merge. This would normally be the simplest method.
0
 
mgmhicksAuthor Commented:
ok, I have this formula that gives a syntax error and I don't know why.  =datediff([renewal_begins],[current_lease_ends],"d")


I have a Renewal_Begins merge field and a current_lease_ends merge field.    I did try it without the brackets and that still did not work.

I not sure how to get the code to stop or show message box after mail merge just to see if I can get code to run on this.

I'm now trying to create my own fields, like I need to know the number of days in the renewal_begins month, or the number of days in the current_lease_ends date.  

This 3rd party app is creating the word document for us, in but how can I access the data that is coming over and maybe manipulate that, instead of the merge fields.

hope that helps clear up my situation.
0
 
Eric FletcherCommented:
If the application is only occasional, or too ad hoc to warrant an automated solution, consider using the little-known Spike feature of Word. The name comes from a mounted spike used to pin notes. I use it to pull selected information from Word invoices into Excel. Here's how...

1. Select the first element you need and press Ctrl-F3. The selected content will disappear, but it actually becomes the first item on the spike. Repeat for each element you need.

2. Move to an empty paragraph mark and press Shift-Ctrl-F3. Each of the elements added to the spike will have their own paragraph in the order they were spiked.

3. To get this to my Excel sheet, I select and copy the dumped spike items and paste it into Excel.

Be sure to close the Word document without saving after you use this!

Note that Spike does not use the clipboard, and its content is unavailable once you have dumped it with Shift-Ctrl-F3.
0
 
GrahamSkanCommented:
mgmhicks,

It is difficult to understand your comment in terms of your question. If you have difficulty in producing a datasource sheet, then it would be better to raise a separate question
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now