Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-31-2019, 04:09 AM
lmoir87 lmoir87 is offline VBA to Populate text content controls in word from excel Windows 7 64bit VBA to Populate text content controls in word from excel Office 2010 64bit
Novice
VBA to Populate text content controls in word from excel
 
Join Date: Jul 2014
Posts: 7
lmoir87 is on a distinguished road
Default VBA to Populate text content controls in word from excel


Hi

I am trying to populate a word document with named ranges/cells in an excel template.

My first iteration was using 'Label' and i was able to get these to populate as follows;

Code:
ThisDocument.Label1.Caption=workbook.Sheets("Sheet1").Range("Label1)
However, I would prefer to use a Rich Text content control, so that the formatting matches the document and so it is possible to click into the content control after it has been populated.

Thanks
LM
Reply With Quote
  #2  
Old 07-31-2019, 06:12 AM
gmayor's Avatar
gmayor gmayor is offline VBA to Populate text content controls in word from excel Windows 10 VBA to Populate text content controls in word from excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,311
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

Populating content controls is easy enough, either
Code:
Dim oCC As ContentControl
Set oCC = ActiveDocument.SelectContentControlsByTitle("title of control").Item(1).Range.Text = whatever
or
Code:
Dim oCC As ContentControl
    For Each oCC In ActiveDocument.ContentControls
        If oCC.TITLE = "title of control" Then
            oCC.Range.Text = whatever
        End If
    Next oCC
The first will produce an error if there is no such named control.

You may find Insert Content Control Add-In useful
__________________
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-31-2019, 01:17 PM
gmaxey gmaxey is offline VBA to Populate text content controls in word from excel Windows 10 VBA to Populate text content controls in word from excel Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,134
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

Graham,


The OP got me to tinkering this morning


Place both the attached files in the same folder. Open the Word document and click in the various controls and you will see how CCs can be populated from:

1. Named columns
2. Named ranges
3. Named sheets
4. Filtered data.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #4  
Old 08-01-2019, 08:34 AM
lmoir87 lmoir87 is offline VBA to Populate text content controls in word from excel Windows 7 64bit VBA to Populate text content controls in word from excel Office 2010 64bit
Novice
VBA to Populate text content controls in word from excel
 
Join Date: Jul 2014
Posts: 7
lmoir87 is on a distinguished road
Default

OK, i got your second code to work but not the first one.

My next question - is there away to loop through all of the Content Control titles/tags and match them to named ranges in Excel and then populate the content control. I have about 30 to go through and i don't want to have to type it out individually in VBA.
Reply With Quote
  #5  
Old 08-01-2019, 08:35 PM
gmayor's Avatar
gmayor gmayor is offline VBA to Populate text content controls in word from excel Windows 10 VBA to Populate text content controls in word from excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,311
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

You can loop through all the controls and apply the appropriate value from the worksheet to the titled control e.g. as follows. You are still going to have to associate 30 titles with the 30 Excel cells

Code:
Dim oCC As ContentControl
    For Each oCC In ActiveDocument.ContentControls
        Select Case oCC.TITLE
            Case "title of control 1"
                oCC.Range.Text = whatever
            Case "title of control 2"
                oCC.Range.Text = whatever
            Case "title of control 3"
                oCC.Range.Text = whatever
                'etc
        End Select
    Next oCC
__________________
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
  #6  
Old 08-05-2019, 07:11 AM
lmoir87 lmoir87 is offline VBA to Populate text content controls in word from excel Windows 7 64bit VBA to Populate text content controls in word from excel Office 2010 64bit
Novice
VBA to Populate text content controls in word from excel
 
Join Date: Jul 2014
Posts: 7
lmoir87 is on a distinguished road
Default

Thank you. This works great.
Reply With Quote
  #7  
Old 10-02-2020, 09:36 AM
rtodd.larsen rtodd.larsen is offline VBA to Populate text content controls in word from excel Windows 10 VBA to Populate text content controls in word from excel Office 2016
Novice
 
Join Date: Oct 2020
Posts: 1
rtodd.larsen is on a distinguished road
Default

Gmayor,

You posted a way to insert text into a rich content control. Is there a way to insert an image of an Excel table into a rich content control. I can't figure out the how I would need to change what you indicated for text for an image. I would really appreciate the help.
Reply With Quote
  #8  
Old 10-03-2020, 02:16 AM
gmayor's Avatar
gmayor gmayor is offline VBA to Populate text content controls in word from excel Windows 10 VBA to Populate text content controls in word from excel Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,311
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

You cannot insert images into text controls. You should use a picture control instead.
__________________
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to Populate text content controls in word from excel Content Controls for large quantities of text tonia8675309 Word 5 06-08-2018 04:42 PM
VBA to Populate text content controls in word from excel Populate Content Control Dropdowns from Excel Deirdre Kelly Word VBA 23 09-07-2017 02:51 PM
VBA to Populate text content controls in word from excel Rich text/Plain text Content Controls in Template michael.fisher5 Word 9 11-19-2014 06:36 AM
VBA to Populate text content controls in word from excel Moving between Rich text content controls Sammie0Sue Word 4 03-12-2014 01:43 AM
VBA to Populate text content controls in word from excel Rich Text Content Controls: Formatting? tinfanide Word VBA 8 03-04-2013 04:15 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:09 PM.


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