Microsoft Office Forums - View Single Post - [Solved] Unique data per record in mail merge chart

aplarsen · 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