Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Charts in a mail merge (Word 2002)

    I am doing a mail merge with around 160 letters. The issue is that each of these letters needs to have a graph on it and the graph needs to be populated with data I have in excel 2002. I am wondering if there is any way to avoid making all of the charts manually, possibly as part of the mail merge. One option I have considered is having the mail merge populate a word table with the data and then somehow linking the graph to the table, I'm not sure how to do that or if it is possible. I have also considered trying to link the graph directly to excel but I don't know if that is possible either. I appreciate any help on this that I can get. Thanks.

  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

    Re: Creating Charts in a mail merge (Word 2002)

    The charts are each different? Because I'm not very experienced linking Word documents to Excel, I'm not sure how you could cook up a link to a nonexistent chart to be drawn from a particular range. Maybe there's a secret way to do that. If not, maybe you could use a macro in Excel to draw and save the charts to disk or into named ranges so that they could be individually referenced and linked during your merge.

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    That is right, all the charts are different. I am dealing with account balances going back six months for about 160 clients. That means 160 letters in the mail merge. Each one will have a chart to show the history of their balances.

    I think I can make a macro to create the charts in excel, but then I wouldn't know how to save "the charts to disk or into named ranges so that they could be individually referenced and linked during" the merge. How would this be done?

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

    Re: Creating Charts in a mail merge (Word 2002)

    See Mail Merge FAQ, especially the "Mail merge to a chart" section. You can download a zip file that demonstrates how to automate a chart in a mail merge.

  5. #5
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    Part of the instructions from the zip file say to save the word file with the data table in it and to substitute the name of the word document for the Const ChartDataDoc value. I don't know how to do this.

    The other part that I am having a hard time with is the instruction line that says "Insert a normal module and a class module into the main merge document's project and copy in the corresponding code." I guess this is something in Visual Basic an area in which I have pretty much no experience.

    Thanks for you help

  6. #6
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    Ok, I've been fiddling around a bit and I think I've answered most of my above questions myself. I am running into one problem though. Every time I try and run the MergeWithChart procedure an error message appears that says "compile error: User-defined type not defined".

    And this part of the code is highlighted: Sub FillDataSheet(ByRef ds As Graph.DataSheet, _
    tbl As Word.Table, chartType As Long

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

    Re: Creating Charts in a mail merge (Word 2002)

    In the Visual Basic Editor, select Tools | References...
    Locate Microsoft Graph 10.0 Object Library and tick its check box, then click OK.
    Visual Basic should now recognize Graph.DataSheet as valid.

  8. #8
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    I have cut and pasted the Codes as you have instructed but now I am getting the same error message near the top of module one in my mail merge document at the text: x As New clsMergeEvents.

    I am supposed to run this macro before a I have done the merge right?

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

    Re: Creating Charts in a mail merge (Word 2002)

    Have you created a class module (Insert | Class Module), named it clsMergeEvents and copied the code from the class module of the same name in MailMergeChartPieLetter.doc into it?

  10. #10
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    Thanks for your help. I now have the macro running and creating charts as part of the mail merge. the only problem now is that I am having trouble getting the data formatted correctly on the graph. I am making a line graph to track balances so I want Dollars along the y axis and days along the x axis. I want the name of the bank in the legend. Right now I have the data stored in rows, which seems to be the right way, but I can put into columns as well.

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

    Re: Creating Charts in a mail merge (Word 2002)

    It's hard to give advice without having an idea of what your document and data look like.

  12. #12
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    Attached is actual data for the month of January. I have changed the client names to bank 1, bank 2, etc. Other than that the data is in the exact same format as the data that I am trying to make charts out of in the mail merge. My mail merge document is just a one page letter that will hopefully have this chart in the middle.
    Attached Files Attached Files

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

    Re: Creating Charts in a mail merge (Word 2002)

    Could you post the merge document with the chart and code too, please?

  14. #14
    New Lounger
    Join Date
    Aug 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Charts in a mail merge (Word 2002)

    Here is the document that I am planning on using for the merge. It will eventually have the letter and lots of other merge fields in it, which are not there yet because I have just been using this doc to test making the charts. The one field that I have put in right now is bank name. I have copied the code into it.
    Attached Files Attached Files

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

    Re: Creating Charts in a mail merge (Word 2002)

    The original version works with a Word document as data source, whereas you are using an Excel workbook. Your Excel table is much too large for Word, so converting it is no option. I don't think the code can be modified easily to work with an Excel data source, unfortunately. If I have time, I will look into it later, but at the moment I'm not optimistic.

Page 1 of 2 12 LastLast

Posting Permissions

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