locked
Word 2007 Mail Merge Chart RRS feed

  • Question

  • I'm trying to figure out how to do a mail merge that also updates a chart in the Word.

    All the data is located in an Excel spreadsheet.  We want to have a chart in the Word document pull in data and update for every mail merge record.

    Thursday, February 9, 2012 2:56 PM

Answers

  • I'm trying to figure out how to do a mail merge that also updates a chart in the Word.

    All the data is located in an Excel spreadsheet.  We want to have a chart in the Word document pull in data and update for every mail merge record.

    AFAIK, you can't update linked chart data during a normal mailmerge process. What you'd need, therefore, is a macro to either:
    • drive the mailmerge, with the macro updating the chart data for you as it processes each record; or
    • update the charts in the output file after the mailmerge has completed.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by Max Meng Friday, February 17, 2012 6:00 AM
    • Marked as answer by Max Meng Monday, February 20, 2012 6:42 AM
    Sunday, February 12, 2012 10:51 PM

All replies

  • To do so, when insert chart by copy and paste, click Paste Options Button imagenext to the chart, and click Chart (linked to Excel data).

    Sincerely,

    Max Meng
    Forum Support


    Come back and markthe replies as answersif they help and unmarkthem if they provide no help.
    If you have any feedback on our support, please contact tnmff@microsoft.com.

    • Edited by Max Meng Sunday, February 12, 2012 1:47 PM
    Sunday, February 12, 2012 1:44 PM
  • Hi Max,

    Perhaps you could explain how that's suppoesed to work with a mailmerge? Surely you don't expect the OP to do a copy & paste for every record?


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, February 12, 2012 10:43 PM
  • I'm trying to figure out how to do a mail merge that also updates a chart in the Word.

    All the data is located in an Excel spreadsheet.  We want to have a chart in the Word document pull in data and update for every mail merge record.

    AFAIK, you can't update linked chart data during a normal mailmerge process. What you'd need, therefore, is a macro to either:
    • drive the mailmerge, with the macro updating the chart data for you as it processes each record; or
    • update the charts in the output file after the mailmerge has completed.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by Max Meng Friday, February 17, 2012 6:00 AM
    • Marked as answer by Max Meng Monday, February 20, 2012 6:42 AM
    Sunday, February 12, 2012 10:51 PM
  • Hi,

    Just checking in to see if the suggestions from Paul were helpful.


    Max Meng

    TechNet Community Support

    Friday, February 17, 2012 6:05 AM
  • Hi,

    I'm trying to figure out how to do a mail merge that also updates a chart in the Word.
    All the data is located in an Excel spreadsheet.  We want to have a chart in the Word document pull in data and update for every mail merge record.

    maybe, following article from Cindy Meister will help you:
    "Special merges | Mail merge to a chart"
    http://homepage.swissonline.ch/cindymeister/MergFram.htm


    Lisa [MS MVP Word]

    • Proposed as answer by Max Meng Monday, February 20, 2012 6:38 AM
    Friday, February 17, 2012 9:14 AM
  • Hi,
    I'm marking Paul's reply as answer as there has been no update for a couple of days.

    Please also check the suggestion provided by Lisa which I think is also helpful.


    Max Meng

    TechNet Community Support

    Monday, February 20, 2012 6:42 AM
  • The Cindy Meister documentation is really hard to understand for someone who doesn't know Visual Basic. I keep getting errors and I've tried to follow all her instructions, but there must be many steps left out that she assumes the user will know. Do you have to understand VB to do this? If so, I will stop trying to make Cindy's steps work. It took me forever just to figure out that I had to go to the Developer tab to get to VB. Then I kind of began to understand that her instructions were telling me to replace text in the VB window (of which there are two... a "general" and a "class").

    Sounds like this is only for folks who get the VB stuff? Or is there someone out there who can write out this process for someone who is a regular Word user?

    Many thanks!

    Friday, March 9, 2012 12:51 AM
  • The Cindy Meister documentation is really hard to understand for someone who doesn't know Visual Basic.

    as there doesn't exist a solution without using VBA, please post your question in a developer forum:
    http://social.msdn.microsoft.com/Forums/en-US/worddev/threads (WebInterface), or Msdn.en-US.worddev (using a newsreader with NNTP Bridge).
    There you will also meet Cindy Meister herself.


    Regards
    Lisa [MS MVP Word]

    Sunday, March 11, 2012 9:22 AM
  • Thanks, Lisa! Much appreciated.
    Monday, March 12, 2012 10:01 PM
  • I just tackled this same issue in Office 2010 but my solution will also work in Office 2007.  It exists completely in Excel:

    1. Whatever form/letter/table & Chart you are trying to use in MS Word, recreate that in Excel.  Mine for example was a letter containing employee benefits and a pie graph breakdown underneath; so I recreated that in Excel. I did not provide instructions for this recreation in this post.

    2. Where MS Word would use the mail merge process to scroll through your existing spreadsheet of data, Excel can do so with multiple VLOOKUP fields referencing your data worksheet.  For one half of this VLOOKUP, you need an ID column in column A of your data worksheet if it does not exist already, which will serve as the leftmost column of your table array. 

    3. In the worksheet containing your recreated your form, use a single cell as your lookup value that will auto-increment with a macro (step 4). Note: this cell can exist on a sheet that, in my macro, will not print.  To demonstrate: if this incrementing cell hits "2", all of your VLOOKUPs will reflect data from the 2nd row of your data worksheet as will your chart/graphic, assuming its driven by the data in your VLOOKUPs fields.

    4. I provided code for two macros, depending on if you want to print now or Save As:

    Sub PRINTALL()
    '
    ' PRINTALL Macro
    ' set ID to a number, then print, cycling all the way to 70
    '
    ' Keyboard Shortcut:
    '
        Dim Counter As Integer
        For Counter = 1 To 70
            Range("O4").Select
            ActiveCell.Value = Counter
            ActiveWindow.SelectedSheets.PrintOut From:=1, To:=32766, Copies:=1, _
            Collate:=True, IgnorePrintAreas:=False
        Next
       
    End Sub

    In my case, the lookup value is in cell "O4", so the macro targets that cell for auto-incrementing (image); edit the macro to match your own scenario. Similarly, the range of your "Counter" should be from 1 to however many records you have (if you want to print them all that is).

    *********************************

    If you don't actually want to print right now, but just create a whole bunch of individualized files, you could use another macro to SaveAs:

    Sub SaveAsWithName()
    '
    ' SaveAsWithName Macro
    ' Use employee's name in SaveAs dialog and cycle through counter
    '

    '
        Dim Counter As Integer
        For Counter = 1 To 70
                 Range("O4").Select
                 ActiveCell.Value = Counter
                 Range("O6").Select
                 FullName = ActiveCell.Value
             ActiveWorkbook.SaveAs Filename:= _
                 "C:\" + FullName + "benefits.xls", _
                 FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                 ReadOnlyRecommended:=False, CreateBackup:=False
        Next
       
    End Sub

    In the Filename code, specify your own file destination.  You may want to create a new folder and point to it in order to collect the volume of files you're about to generate.

    "O6" is a cell with a concatenated VLOOKUP for an employee's name, based on "O4", my aforementioned lookup value; alter for your own scenario.  My formula for this cell ended up being pretty lengthy to account for folks with spaces in their names when complying with a "no spaces" file naming convention:

    =CONCATENATE(SUBSTITUTE((VLOOKUP(<parameters>))," ",""),(SUBSTITUTE((VLOOKUP(<parameters>))," ","")))

    The end result driving my file name is:

    *************************************************

    The end result of either of these macros is a set of files (printed or digital), each containing its own set of data and corresponding graph, and each named according to the employee's name if necessary.

    Whether or not this works for your original needs, I wanted to post my solution somewhere because I did not find a good answer to this issue in my own search!

    Cheers!

    Tuesday, June 26, 2012 1:59 AM