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: 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

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
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
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

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: 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

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: 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

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
  #9  
Old 11-22-2021, 08:22 PM
LauraBlair LauraBlair 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 2019
Novice
 
Join Date: Nov 2021
Posts: 5
LauraBlair is on a distinguished road
Default

Hi all, I am doing something similar to the original poster, but I cannot get your original code to work with the loop, any assistance would be greatly appreciated.

I have named the cells in excel, and i have given all content controls in the work doc a title, but still wont work. Could you please assist me in getting this code to work?

Thank you so much



Code:
Sub Exec_Summary2()
'
Dim objWord As Object
UserName = Environ$("username")
Dim oCC As ContentControl


Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate

objWord.Documents.Open ("https://XXXX-my.sharepoint.com/personal/" & UserName & "_XXXX/Documents/_MyProfile/Desktop/Business%20Executive%20Summary%20MASTER.docx?web=1")

    For Each oCC In ActiveDocument.ContentControls
        Select Case oCC.Title
            Case "CASENAME" 'This is the name being referenced for CC in Word
                oCC.Range.Text = CAseName 'This is the cell being referenced in excel
            Case "title of control 2"
                oCC.Range.Text = whatever
            Case "title of control 3"
                oCC.Range.Text = whatever
                'etc
        End Select
    Next oCC

End Sub
Reply With Quote
  #10  
Old 11-22-2021, 10:20 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 2019
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

The following should work, provided your sharepoint path is correct
Code:
Sub Exec_Summary2()
'
Dim objWord As Object
Dim oDoc As Object
Dim sUserName As String: sUserName = Environ$("username")
Dim oCC As Object

    On Error Resume Next
    Set objWord = GetObject(, "word.application")
    If Err.Number <> 0 Then
        On Error GoTo 0
        Set objWord = CreateObject("Word.Application")
    End If

    objWord.Visible = True

    Set oDoc = objWord.Documents.Open("https://XXXX-my.sharepoint.com/personal/" & UserName & "_XXXX/Documents/_MyProfile/Desktop/Business%20Executive%20Summary%20MASTER.docx?web=1")
    For Each oCC In oDoc.ContentControls
        Select Case UCase(oCC.Title)
            Case "CASENAME"    'This is the name being referenced for CC in Word
                oCC.Range.Text = Range("CAseName")   'This is the cell being referenced in excel
            Case "TITLE OF CONTROL 2"
                'oCC.Range.Text = whatever
            Case "TITLE OF CONTROL 3"
                'oCC.Range.Text = whatever
                'etc
        End Select
    Next oCC
Set objWord = Nothing
Set oCC = Nothing
Set oDoc = Nothing
End Sub
__________________
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
  #11  
Old 11-22-2021, 10:36 PM
LauraBlair LauraBlair 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 2019
Novice
 
Join Date: Nov 2021
Posts: 5
LauraBlair is on a distinguished road
Default

Thank you so much, works perfectly
Reply With Quote
  #12  
Old 11-22-2021, 11:07 PM
LauraBlair LauraBlair 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 2019
Novice
 
Join Date: Nov 2021
Posts: 5
LauraBlair is on a distinguished road
Default

Sorry, another question, as the Word doc that is opened is a master file, can i at the end of the code do a save as to a specific folder so it never saves over the master file that is opened? Thank you
Reply With Quote
  #13  
Old 11-23-2021, 04:44 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 2019
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

If the document is being used as a template then instead of opening the file create a new file from it i.e.
Code:
Set oDoc = objWord.Documents.Add("https://XXXX-my.sharepoint.com/personal/" & UserName & "_XXXX/Documents/_MyProfile/Desktop/Business%20Executive%20Summary%20MASTER.docx?web=1")
That document is then unnamed, so you can add a line to save it wherever you want with whatever name you want.
__________________
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
  #14  
Old 11-24-2021, 01:19 AM
LauraBlair LauraBlair 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 2019
Novice
 
Join Date: Nov 2021
Posts: 5
LauraBlair is on a distinguished road
Smile

It works perfectly. Thanks for all your help
Reply With Quote
  #15  
Old 05-25-2022, 09:51 PM
LauraBlair LauraBlair 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 2019
Novice
 
Join Date: Nov 2021
Posts: 5
LauraBlair is on a distinguished road
Default

Hi @gmayor, you were a massive help with the above coding, and i have used this many times now, but with a current project, I am having issues for the document file path to open the specified word document, and then in the coding to save as to the downloads folder on user profile. But the users of this new project, have varying drives, and profiles are not set up the same. I am hoping you may be able to assist in a code where it searches for the users profile / drive, but with always the same document file name? Thanking you in advance.
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

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


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