Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-15-2018, 10:15 AM
aplarsen aplarsen is offline Unique data per record in mail merge chart Windows 10 Unique data per record in mail merge chart Office 2016
Novice
Unique data per record in mail merge chart
 
Join Date: Feb 2018
Posts: 2
aplarsen is on a distinguished road
Default Unique data per record in mail merge chart

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.
Reply With Quote
  #2  
Old 02-15-2018, 02:18 PM
macropod's Avatar
macropod macropod is offline Unique data per record in mail merge chart Windows 7 64bit Unique data per record in mail merge chart Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-16-2018, 04:01 PM
aplarsen aplarsen is offline Unique data per record in mail merge chart Windows 10 Unique data per record in mail merge chart Office 2016
Novice
Unique data per record in mail merge chart
 
Join Date: Feb 2018
Posts: 2
aplarsen is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
chart, mailmerge, source

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unique data per record in mail merge chart 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
Unique data per record in mail merge chart 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
Unique data per record in mail merge chart 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:23 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft