Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2019, 02:39 AM
guiltyvictim guiltyvictim is offline Populating multiple Content Controls after mail merge Windows 7 64bit Populating multiple Content Controls after mail merge Office 2010
Novice
Populating multiple Content Controls after mail merge
 
Join Date: Jul 2019
Posts: 2
guiltyvictim is on a distinguished road
Default Populating multiple Content Controls after mail merge

Hi guys, first time poster!

So I'm helping a colleague optimise some electronic forms. Basically it's currently setup as this:
  1. People fill in a form to bid for some charitable funds
  2. The forms' data are imported into a single spreadsheet
  3. The data is then mail merged into a word template, for scoring purposes. Each document will contain all the bids, and the documents will be sent out to the charity's trustees to do the scoring
  4. Finally all the scored word documents' are imported back into excel to summarise the scores amongst all trustees before decisions are made
Now my colleague's templates basically carry no content control information (title or tags), so he's literally just dumping each word document's content control data into a single line per document.

This means despite there being say 20 bids for each trustee to score, the final step generates 1 line of data, which he'd then copy and paste into another worksheet. All his calculation formulas therefore use direct cell references, and thus mistakes were made and it was impossible to read the formulas and find the errors.

So here's my plan: at step 3, add a bid reference number as a content control for each bid. During import, the VBA code in my excel will look for the reference number field, and start a new line of data.

The problem is:
  • I can't mail merge the reference number into the content control directly, so the most obvious solution is out.
  • I can bookmark the mergefield, and I found the code to get the bookmarked range's text. I also found the code to populate content controls, but I don't see how I can do this after a mail merge, ensuring that each bid's ref no will be populated to the content control within the same bid.
  • I'm also looking at whether I can just merge the ref number into the header, as I know there's VBA code to grab the header / footer text. But I don't know how to get the content control to grab the header text for the section that it belongs to.
So any suggestions on how I can best tackle this would be greatly appreciated!

Thank you in advance!
Reply With Quote
  #2  
Old 07-30-2019, 04:22 AM
gmayor's Avatar
gmayor gmayor is offline Populating multiple Content Controls after mail merge Windows 10 Populating multiple Content Controls after mail merge Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Quote:
I can't mail merge the reference number into the content control directly, so the most obvious solution is out.
Not necessarily. My old friend Greg Maxey has an interesting custom mail merge application at Enhanced Merge (Merge Many to One) which employs content controls rather than merge fields and will work with Excel and Access data sources.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 07-30-2019, 07:45 AM
guiltyvictim guiltyvictim is offline Populating multiple Content Controls after mail merge Windows 7 64bit Populating multiple Content Controls after mail merge Office 2010
Novice
Populating multiple Content Controls after mail merge
 
Join Date: Jul 2019
Posts: 2
guiltyvictim is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Not necessarily. My old friend Greg Maxey has an interesting custom mail merge application at Enhanced Merge (Merge Many to One) which employs content controls rather than merge fields and will work with Excel and Access data sources.
Thank you for the suggestion! It's a pretty amazing feat that someone's built that in macros!!

Unfortunately after downloading and playing with it I've noticed that it outputs individual word documents from the merge, and not a single document with all the records, which is what I need.


The good news is, after looking around for what can be done with VBA, I've come up with a solution that works for my purpose, and I'll share it just in case it can help with anyone else:

Since merged documents have individual sections, with their independent headers and footers, I know I can populate the reference number in the header of each bid.

The next problem is I didn't know how to reference the content controls per section, but turns out it's easily done, by using Section.Range.ContentControls.

So the solution is:

- Put the reference number merge field inside the header of your pre-merged word template.
- Use the following VBA code:

Code:
For Each Sec In ActiveDocument.Sections
    For Each CCtl In Sec.Range.ContentControls
        If CCtl.Title = "ref_no" Then
            CCtl.Range.Text = Sec.Headers(wdHeaderFooterPrimary).Range.Text
        End If
    Next
Next
The code basically loops through each section, then loops through each content control within that section, checking whether they're the content control we're look for. Then it just puts whatever's in the header into the content control.

The limitation is probably obvious, but this approach basically means you can't have anything else within your header. Although at the same time you can probably do something clever like using a delimiter to truncate unwanted header content, whilst putting an invisible (white on white, font size 1) merge field into the header as the very first thing.

Hope it's helpful to anyone else!
Reply With Quote
  #4  
Old 07-30-2019, 09:27 AM
gmaxey gmaxey is offline Populating multiple Content Controls after mail merge Windows 10 Populating multiple Content Controls after mail merge Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

You could put your header merge field in a richtext CC titled "Ref"

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oSec As Section
Dim oCC As ContentControl, oCC2 As ContentControl
  For Each oSec In ActiveDocument.Sections
    Set oCC = oSec.Headers(wdHeaderFooterPrimary).Range.ContentControls(1)
    For Each oCC2 In oSec.Range.ContentControls
      If oCC2.Title = "ref_no" Then
       oCC2.Range.Text = oCC.Range.Text
       'If there is only one "ref_no" CC per section then:
       'Exit For
      End If
    Next
  Next
lbl_Exit:
  Exit Sub
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating multiple Content Controls after mail merge Populating Tables with data when available only - Using Mail merge stevejohhnym Mail Merge 1 06-25-2019 12:50 AM
Populating multiple Content Controls after mail merge Delete or merge Content controls with the same name sylvio Word VBA 9 08-30-2017 01:46 AM
Macro to save docx to doc that checks compatibility and converts content controls to static content. staicumihai Word VBA 4 10-12-2016 08:23 PM
Populating multiple Content Controls after mail merge Inserting multiple content controls for a date jeffreybrown Word 2 05-09-2016 05:53 PM
Mail Merge Field Not Populating JennEx Mail Merge 3 05-10-2015 09:30 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04: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