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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. I will check this out tonight and write back with any questions/comments. Appreciate it!
- Diane
- Diane
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
- Diane
ASKER
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
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
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
It would not be so bad if you had at least replied to each proposal with why it was not viable.
JeffCoachman
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.
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
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
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
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
;-)
Jeff
ASKER
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
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
Thanks everyone for helping out with this! I learned much!
- Diane
- Diane
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!
Interesting post . BTW , if someone was looking for a permit search , my family filled a template document here "<a>https://goo.gl/ZpmKBs</a>".