I have a Word document that is used as the source document for a mail merge. I can edit the document, but not the data being used for the merge. I need to transform some of the data in the data source (specifically, I need to take numbers (e.g. 342) and add their value in words (e.g. "three hundred forty-two (342)")). I can write a VBA function to do the transformation, but I'm not sure how best to get the data to that function.
Is there some way I can associate a macro with specific points in the document and let the merging drive the transformation process? (I'm thinking of how you can use formulas in Word fields; I have a few things of the form { IF { MERGEFIELD foo } > 75 { MERGEFIELD foo } { = { MERGEFIELD foo } * 20 } } in the document already. If I could add something so I could go { FUNCTION WordNum { MERGEFIELD number } }, that would be ideal.)
Alternately, I think I can use VBA to rummage around in the mail merge's datasource (specifically document.MailMerge.DataSource) and rewrite fields. If I go that route, where should I execute the macro so that it will get to the data after it's been read from the data source, but before it's been merged with the document?
There does not appear to be a way to call arbitrary functions from embedded Word macros, so I went the VBA route.
I added an AutoOpen macro to the source document that called MailMerge.EditDataSource and then walked through the table to make its changes.