Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Talking Dynamic Charts in Word Mail-Merge?

    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:
    wopchart.JPG

    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.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •