#1
|
|||
|
|||
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) Thanks LM |
#2
|
||||
|
||||
Populating content controls is easy enough, either
Code:
Dim oCC As ContentControl Set oCC = ActiveDocument.SelectContentControlsByTitle("title of control").Item(1).Range.Text = whatever 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 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
Thank you. This works great.
|
#7
|
|||
|
|||
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. |
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
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 |
#11
|
|||
|
|||
Thank you so much, works perfectly
|
#12
|
|||
|
|||
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
|
#13
|
||||
|
||||
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")
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#14
|
|||
|
|||
It works perfectly. Thanks for all your help
|
#15
|
|||
|
|||
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.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Content Controls for large quantities of text | tonia8675309 | Word | 5 | 06-08-2018 04:42 PM |
Populate Content Control Dropdowns from Excel | Deirdre Kelly | Word VBA | 23 | 09-07-2017 02:51 PM |
Rich text/Plain text Content Controls in Template | michael.fisher5 | Word | 9 | 11-19-2014 06:36 AM |
Moving between Rich text content controls | Sammie0Sue | Word | 4 | 03-12-2014 01:43 AM |
Rich Text Content Controls: Formatting? | tinfanide | Word VBA | 8 | 03-04-2013 04:15 AM |