Avatar of zgrrl
zgrrl asked on

Creating Unique Pie Charts In Mail Merge Document

Hello,
In doing some quick searches here, it appears this question has come up however, still not seeing a definitive answer. Here is what I am trying to do. I have a large data file (approx 1500 rows of data). I want to merge parts of this
data into a mail merge document to make individualized statements. Some of the data needs to create unique pie chart for each data row. How is this best completed? I keep finding info pointing to Cindy Meister online, however, I am not versed in VBA and it seems like there are pieces of the process left out for us newbies.  Any help much appreciated. Seems like it would be a common request to do something like this...  Is this something more easily done with Access? Or will excel data and word (or even Publisher) work?
- Diane
Microsoft AccessMicrosoft Word

Avatar of undefined
Last Comment
Danyell Almanza

8/22/2022 - Mon
GrahamSkan

I can believe that it would require some (perhaps a lot of) VBA. If anyone knows then Cindy does. Can you post the link with the VBA that you need to understand?
ASKER
zgrrl

Hi GrahamSkan,

Here is the info I found from Cindy at this url http://homepage.swissonline.ch/cindymeister/MergFram.htm

Word mail merge doesn't provide any way to generate a chart for each record in a mail merge. There are four basic approaches you can use to create charts for mail merge:

    * Create a chart for each record in Excel. Add a column to the data table and enter the name of the appropriate chart for each record. Use this merge field in LINK field in the mail merge document.
    * Use a Database field in the main merge document to create a data table for each merge record (for details, see http://www.knowhow.com/Guides/DatabaseInfo/DatabaseInfo.htm). Select the table and link it to an MS Graph. Preview the merge data, one record at a time, and print as you go. (Executing the merge would remove the bookmark that links the table to the chart, so you'd get the same chart for all records.)
    * Create the chart for each record in the mail merge result document, after the mail merge has executed.
    * Create the charts on-the-fly, as the mail merge executes, in the main merge document.

The first three can be done manually, and are possible in all versions of Word; using VBA (macros) would make the process much faster, of course.

The fourth method relies on the Mail Merge Events introduced in Word 2002 to manipulate the main merge document as each record is merged. The sample files in MrgChart.zip provided here uses this method, automating an MS Graph object embedded in the main merge document. You can certainly derive the code necessary automating for the third method based on the code in this sample, as all the basics required for automating MS Graph are present.

I downloaded the zip files she provides. I read the directions file. When I open up the text main document, I see that it grabs the first record info to make the piechart, but I don't know where I would get any VBA code to make this continue for the additional records. I feel I must be missing something, perhaps staring me in the face!

THanks,
Diane
SOLUTION
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
zgrrl

Thanks guys. I will check this out tonight and write back with any questions/comments. Appreciate it!
- Diane
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
zgrrl

Helen, just to clarify, are you saying that I would have to save each document with unique chart as a PDF? If so, that would not be practical for 1500 statements. I'm I understanding you correctly?

- Diane
ASKER
zgrrl

Hello All,

I have kinda gone down another road to see if this can be done differently and now have another question. I've used an add-in called MicroCharts that creates in cell graphs for each record. This has a feature to convert the graphs to pictures. So now, I have a nice little unique pie chart for each record.
Now I have made a document in Publisher and want to merge this field (the field containing the unique pie chart) into my publisher document. Seems like almost there... however, Publisher does not "see" there is any content in the merge field (the cell I'm referencing from excel). I'm thinking there is a step I'm missing in excel to let it now that the cell contains the image/picture, but that's just a guess.

Any ideas here? Or should this be posted as new question? I'm attaching my excel file so you can take a look. This is after the microcharts have been converted to images.
- Diane
 dummydata-test-micrographs.xls
Jeffrey Coachman

If you have "gone down another road", then AFAICT, you have rendered your original question invalid.

It would not be so bad if you had at least replied to each proposal with why it was not viable.

JeffCoachman

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GrahamSkan

You now have a couple of new problems.

In Word, you would need to use the IncludePicture field, and that expects a picture file, not a part of a spreadsheet. You would have to save the images as separate files and put their addresses in their place.

Expanded, the field codes would look like this.

{ INCLUDEPICTURE { MAILMERGE CharTest } }

The other problem is that Publisher doesn't have such a field.
ASKER
zgrrl

Hi All,

Thanks for all your comments and suggestions. All of them have been useful in discovering what I can and can't do -- will be testing MS Access approach today and will respond to all as suggested by JeffCoachman above.

 - Diane
Jeffrey Coachman

Yes, not that I thought that my proposal was best, it was just that you had not really replied to: Helen, Graham, or me with what you thought of our suggestions, before you decided to: "go down another road"

;-)

Jeff
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
zgrrl

Jeff,
Yes - am going to get back to all. Will experiment with all proposed. Awaiting getting updated version of MS Access on my machine....  in the meantime I was trying all kinds of things!
- Diane

ASKER
zgrrl

Thanks everyone for helping out with this! I learned much!
- Diane
Helen Feddema

There are always many ways to do things in Office, at least since all major Office components got object models that can be manipulated in VBA code.  I agree that generating 1500 PDFs would not be practical!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Danyell Almanza

Interesting post . BTW , if someone was looking for a permit search , my family filled a template document here "<a>https://goo.gl/ZpmKBs</a>".