Dynamic Charts in Word Mail-Merge?

Topic

New Reply

We have conducted a Survey for more than 100 companies, and we would now like to give them personalized Reports on their results. We obviously know how to merge this into Word, but we’d like each company’s Report to have a few nice Charts in them that compare them to their Sector’s average.

Eg, if this were Coca Cola’s personalized Report, it would look as follows:

Name: Coca Cola
Sector: Food & Beverages
Score: 100%
Sector Average: 75%

Below that would be a chart:
29435-wopchart

Of course, if I put a chart it Word, it pulls the values from Excel–not itself, so I can’t see how to get the values in Word to change based on the values in the document. I assume the Pivot Chart in Excel won’t cycle through and change/update based on what Word is reading from it at the time, so I’m thinking that maybe this isn’t possible, but I’m hoping I’m missing something obvious.

Anyone have any idea if what we’re hoping to do is possible, or will we need to copy/paste the charts one-at-a-time into Word?

Many thanks in advance for any help or guidance anyone can provide.

Reply | Quote

Replies

I think it would be better to include an image rather than a live Excel reference. Would it be possible to automate Excel to export individual charts to a JPEG or PNG image, and then, using a merge field, insert the chart names into INCLUDEPICTURE fields? (I can never remember how ordinary fields and merge fields interact.) After the images are displayed in the document, you would want to convert the INCLUDEPICTURE fields to their result by selecting them and pressing Ctrl+Shift+F9, or perhaps you can automate that with a macro.

Reply | Quote

Thank you for your reply, but we’re still having to create 100+ images manually. If it were a Pivot Chart, the Chart would change based on the current value, although I imagine that would also involve major programming? We’re just trying to circumvent having to create 100+ Charts and then have to put them into each document. Even if the INCLUDE PICTURE Field worked and we did Ctrl+Shift+9, we’d still have to open the 100+ documents.

I figure this is maybe going to be impossible, but I thought someone might have found a clever way around this problem?

Thank you, again, though for your reply.

Reply | Quote

As an alternative to merging from the Word side, you could automate Word from the Excel side. For example, you would set Excel to loop over the list of companies (presumably somewhere in your workbook), populate the Word document with all needed content, and then save with a file name based on the company name.

On the Word side, you might be able to use your merge document as is. (However, to avoid confusion, it be better to replace the merge fields with other placeholders and disconnect from the data source.)

On the Excel side… my Excel VBA is too weak to assist here.

This kind of approach can take quite a bit of time to develop, but if you envision doing this every quarter (or more often), then the effort might be justified.

Reply | Quote

Fair enough. Thanks for your reply. Bottom line: there’s no easy way to dynamically create charts in a Word Mail Merge. Maybe a few versions of Office down the road!?
Many thanks again for your replies.
Brian

Reply | Quote