Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating chart during mail merge (Office XP/Word)

    I have a fairly simple Word document with a couple of small tables, one of which drives a two-slice pie chart showing a two-number percentage split (i.e., 25/75 percent). Works great on a single standalone document, but am having issues with getting this to work with a mail merge (i.e., I have 1000+ forms to generate for a bunch of folks, each with a different percentage split I wish to graphically represent.)

    Can I even do this with Word and the delivered Pie Chart object? Or is there another sneakier way to do this? I couldn't figure out immediately how to set this up via the menu options, I started thinking VBScript would be the fall-back option... Somehow setting the Pie Chart properties via code or something. But, I do not know the properties that would need setting? Investigation/research may be in order...

    Is there a straightforward way to do this? Any help would be greatly appreciated.

    Thanks. This site is a fantastic resource BTW, I just now have found it.

    Regards,
    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating chart during mail merge (Office XP/Word)

    I don't think this can be done in Word without macros, but I'll be happy to be proved wrong.

    Any chance that you can create this as a report in Access? There, you can combine text and charts that vary from record to record without any coding.

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Yeah, that's what I was leaning towards (macros, perhaps)... However I'm stuck as to where to look for the correct object properties to reference, to 'remotely' populate the chart object with the two numbers/data points (i.e., two percentages) that I'm interested in representing.

    Is there anyone that might know of a way -- a link from the table to the graph, perhaps? -- or a suggestion in terms of coding or direction that might be helpful?

    I'm in your debt....

    Regards,
    Scott

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: updating chart during mail merge (Office XP/Word)

    If all you want is a simple Pie Chart with 2 segments that always add up to 100% then you could draw it using Autoshapes.
    The attached Word Document has two shapes called "Pie1" and "Pie2".
    The Macro SetPieSizes takes a single parameter that sets the angle for Pie1
    Pie2 then gets set to an angle of 360 - Pie1

    StuartR
    Attached Files Attached Files
    • File Type: doc x.doc (31.5 KB, 3 views)

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating chart during mail merge (Office XP/Wo

    Hi Stuart:
    I took a look at your macros. Why does the "TestMe" macro show up when I press Alt+F8, but not the SetPieSizes macro? Both of them show in the VBE. Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: updating chart during mail merge (Office XP/Wo

    > Why does the "TestMe" macro show up when I press Alt+F8, but not the SetPieSizes macro?
    Because SetPieSizes has an input parameter. This leads to a good way of hiding Macros from the Word user interface, by specifying an Optional dummy parameter.

    StuartR

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: updating chart during mail merge (Office XP/Word)

    If you had the 100 possible pie charts saved as BMP files, you could try to merge in the file name for an INCLUDEPICTURE field. (I don't know if a merge can populate a file name into a field code, but "there must be a way" if you try hard enough.) A macro solution probably would be easier. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating chart during mail merge (Office XP/Wo

    Thanks. Learn something every day--well, almost every day. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Stuart,

    A few things:

    very clever.

    I tried recording a macro while I adjusted the size of your pies. I got a statement like
    ActiveDocument.Shapes("Pie1").Select
    Selection.ShapeRange.Adjustments.Item(2) = 145.6651
    Selection.ShapeRange.Adjustments.Item(1) = 76.2491
    where the item index referred to which handle I was rotating.

    So what are the "adjustments" of which there seem to be 2 for each pie piece - are these the yellow "handles" by which one rotates the pie piece (never did know what to call these things)?

    What would be the diff between the way you adjusted the pie slices and the way that was recorded by the macro recorder? They seem to be equivalent.

    How does one work this into the mail merge per the original request (maybe I'm just dense today). I'll assume the record has a number, perhaps in degrees, for one of the pies. As the record is being processed, that field is represented by some merge field. How does one take that, pass it to your SetPieSizes macro (even thru TestMe) to get the pies drawn?

    Thanks

    Fred

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Fred,

    Item returns a member of a collection, in this case Adjustments.Item(1) is exactly the same as Adjustments(1).
    The adjustments give the position (in degrees) of the 2 yellow handles that adjust the Pie segment

    I really don't know how to get this into a mail merge, I've never really been into mail merge, I was hoping someone else might provide that bit.
    (I was responding to "I'm stuck as to where to look for the correct object properties to reference, to 'remotely' populate the chart object with the two numbers/data points" in <post#=243897>post 243897</post#>)

    StuartR

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Hi Stuart,

    thks for the quick vba lesson.

    ok, so the solution wasn't complete. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    still learned a valuable lesson.

    fred

  12. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Stuart has provided one way forward which you could implement relatively easily if you bring one of the two values in via the merge (the other value isn't needed as it has to add to 100%). The macro could then be run using that value as its input. You might want to hide the merged value as it may not provide any useful function other than providing the input value.

    For this method to work easily you would need to be merging to separate documents. The job would be considerably more complex if you merge to a continuous document as the shapes names would not be the same in subsequent records and the input value is harder to harvest.

    Other options include the already mentioned Access option which is clearly the cleanest option. This will require a large investment in knowledge to set up if you haven't used Access before.

    A more palatable but messier option would be to use Excel to create all the charts in advance and store them all in that file. You may need a macro to create the multitudes of charts but it should be simple to create. Then the Word merge could import a path to the particular relevant graphic. If that path was used inside an IncludePicture Field then it should update easily. Note I haven't set this up so I can't guarantee that it will work but I can't forsee any reason why it wouldn't.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Another alternative would be to use automation from either Excel or Access to build the series of documents (or one multi-section document) - it's a common technique we use when we need complex formatting beyond the reporting capabilities of Access, and it works as well or better than mail merges in most cases, though it does get fairly involved from a VBA perspective.
    Wendell

  14. #14
    New Lounger
    Join Date
    Apr 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating chart during mail merge (Office XP/Word)

    Wow, thanks to all for the excellent guidance... Some very interesting solutions to this problem, I appreciate it.

    There are clearly several options to pursue, I will investigate all.

    Thanks again everyone!!

Posting Permissions

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