In Word, how can I alter mail merge data programmatically with VBA?

Question

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?

Solution: I ended up going 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.

Answer 1

Could you do the data transformation in Microsoft Query? That is, where you currently have:

Data Source -> Mail Merge Template

create a Microsoft Query that sits between your data source and Word:

Data Source -> Microsoft Query -> Mail Merge Template

It's been a while since I used Mail Merge in Word but I don't remember being able to exert much control over it...