Unique data per record in mail merge chart

aplarsen · #1 02-15-2018, 10:15 AM

Experienced Mail Merge and VBA user.

I need to make a graph per record where 3 data points are the same but the 4th is unique to that record. It's a column graph showing student performance on a test with school, district, and state averages for comparison. Everything else about the score report is easy with a normal Mail Merge. That one per-record score is where I am stuck.

I have two thoughts...write the macro to go from a template and essentially copy-paste that template into new pages for each record, then edit the source data to change that one cell for each student. This is kind of a poor man's Mail Merge. I see that there are methods for interacting with the chart object, but I can't find one for editing the source data. I also can't find a way to specify literal data as the source. It only seems to want a string that is an Excel-type reference.

The other thought would be to still do a Mail Merge, but then have a macro go back through all of the charts and again edit the source data in the embedded Excel-like table.

Does one seem easier than the other? The trouble I'm really having is that recording a macro isn't an option, which is my usual MO. When a macro is recording, it's not possible to interact with the chart objects via the mouse. Letting MS write that VBA so I can edit it is how I usually get started, since I generally know what I want to do but don't know the methods and properties to edit without reading lots of docs.

macropod · #2 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 · #3 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

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