Unique data per record in mail merge chart

macropod · #1 02-15-2018, 02:18 PM

You may want to try the Many-to-One Mail Merge add-in from Doug Robbins at http://bit.ly/1hduSCB. In addition to a 'Many to One' merge, it handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple

aplarsen · #2 02-16-2018, 04:01 PM

I'd seen that in a couple of threads, but it looks like overkill for what I want. I also really like to write these things myself if possible. Doug's solution is probably awesome, but I'm trying to stay simple.

I realized that I could probably get into the table of data after the chart and pull out those values, and once I went down that path, I was golden. This is the eventual macro that is run after the MM is done. It just iterates through each chart object, finds the next table after it, and copies the data into the 4 cells that are needed for that chart. It is a little goofy to be printing the data on the page just to put it into the graph, but that's actually how the original template looked anyway.

Code:

For Each Shape In ActiveDocument.InlineShapes
    Shape.Select
    Selection.GoToNext wdGoToTable
    
    studentscore = Val(Selection.Tables(1).Cell(2, 2).Range.Text)
    schoolscore = Val(Selection.Tables(1).Cell(2, 3).Range.Text)
    districtscore = Val(Selection.Tables(1).Cell(2, 4).Range.Text)
    statescore = Val(Selection.Tables(1).Cell(2, 5).Range.Text)
        
    Shape.Chart.ChartData.ActivateChartDataWindow
    
    Shape.Chart.ChartData.Workbook.Worksheets(1).Cells(2, 2).Value = studentscore
    Shape.Chart.ChartData.Workbook.Worksheets(1).Cells(3, 2).Value = schoolscore
    Shape.Chart.ChartData.Workbook.Worksheets(1).Cells(4, 2).Value = districtscore
    Shape.Chart.ChartData.Workbook.Worksheets(1).Cells(5, 2).Value = statescore
    
    Shape.Chart.ChartData.Workbook.Close
Next Shape

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail merge doesn't pick up the inserted content after repeated data of last record	d3w	Mail Merge	2	01-18-2018 03:46 PM
Mail Merge Next Record If rule	RHensley	Mail Merge	10	03-07-2017 08:05 AM
Using mail merge to make place cards. One record with data over two pages	drew.last	Mail Merge	2	03-05-2015 04:37 PM
Mail Merge URL - a unique question	kitefrantic	Mail Merge	1	12-31-2013 07:34 PM
avoid duplicete record and merge the record with the existed record	hemant.behere	Excel	0	01-10-2012 02:53 AM