#1
|
|||
|
|||
Populate Word content control fields with data from Excel
Hello, I have about 50 fields set as Rich text content controls, some fields are repetitive, in a word document. As some these field values involve calculation, it will be tremendously helpful to map the data for these content controls from an excel file. This will need VBA, I am guessing.
I am a total newbie at this. I have searched some the previous postings but have not made any headway. This one by "gmayor" was helpful but I am not sure how this code gets executed from excel and populates a particular word document. There is no submit or run button from what i can tell. https://www.msofficeforums.com/word-...ord-excel.html I am hoping someone can give me a start with one or two fields to map from excel to word CC and explain how this moves from excel to word, that will be really appreciated. Maybe it is as simple as saving the excel file? Sorry, if I sound stupid but that is where I am. Thanks a bunch. |
#2
|
||||
|
||||
The following should help. The Excel macro assumes a two column worksheet with a header row. Column 1 has the titles of the content controls in the named document, column 2 has the values associated with those controls.
The macro runs through each row in column 1 and populates any matching control titles in that column with the associated values from column 2. You can run the macro from a button on the worksheet, or from a button on the ribbon or QAT. Code:
Sub PopulateCCs() Const sPath As String = "c:\path\document name.docx" 'the full path of the document Dim oCC As Object Dim wdApp As Object Dim wdDoc As Object Dim LastRow As Long, lngIndex As Long Dim xlSheet As Worksheet Dim sValue As String On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 ActiveWorkbook.Save Set wdDoc = wdApp.Documents.Open(sPath) wdApp.Visible = True Set xlSheet = ActiveSheet With xlSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For lngIndex = 2 To LastRow For Each oCC In wdDoc.ContentControls If UCase(oCC.Title) = UCase(.Cells(lngIndex, 1)) Then oCC.Range.Text = .Cells(lngIndex, 2) Exit For End If Next oCC Next lngIndex End With lbl_Exit: Set wdApp = Nothing Set wdDoc = Nothing Set oCC = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thank you so much. This was super helpful.
|
#4
|
|||
|
|||
If you don't mind, one other question. Is there a way to protect the word document created above? I changed the statement below so that the original file is retained.
Set wdDoc = wdApp.Documents.Add(sPath) Can I protect this document when created? Thanks. |
#5
|
||||
|
||||
Protect it for what purpose?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
Hi, Sorry, I should have been more specific. I want to protect the entire document content, including the content control data imported from excel, when this new document gets created. I set up protection on the template but that is not retained when the new doc is generated. Thanks.
|
#7
|
||||
|
||||
OK, but what do you want to protect it from?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
Hi, I would like the protection to prevent the document from being changed (CC and the other content in the doc) once the word file gets created. I am imagining similar to how we can lock an excel sheet. Maybe this is wishful thinking. Thanks.
|
#9
|
||||
|
||||
OK. Once the following macro is run, the document is protected as read only - or you could save as PDF.
Code:
Sub PopulateCCs() Const sPath As String = "c:\path\document name.docx" 'the full path of the document Dim oCC As Object Dim wdApp As Object Dim wdDoc As Object Dim LastRow As Long, lngIndex As Long Dim xlSheet As Worksheet Dim sValue As String Const sPassword As String = "MyPassword" On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 ActiveWorkbook.Save Set wdDoc = wdApp.Documents.Open(sPath) If Not wdDoc.ProtectionType = -1 Then wdDoc.Unprotect Password:=sPassword End If wdApp.Visible = True Set xlSheet = ActiveSheet With xlSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For lngIndex = 2 To LastRow For Each oCC In wdDoc.ContentControls oCC.LockContents = False If UCase(oCC.Title) = UCase(.Cells(lngIndex, 1)) Then oCC.Range.Text = .Cells(lngIndex, 2) Exit For End If oCC.LockContents = True Next oCC Next lngIndex End With wdDoc.Protect Type:=3, Password:=sPassword lbl_Exit: Set wdApp = Nothing Set wdDoc = Nothing Set oCC = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Importing content control data from excel and populate two fields on selected dropdown | badarlodhi | Word VBA | 8 | 02-08-2023 10:47 AM |
Push Word content control data and excel cells | shaztastic | Word VBA | 13 | 08-27-2018 06:46 AM |
How can I set up tab to go back and forth between legacy fields and content control fields in a Word | Ikajed | Word | 1 | 10-13-2017 06:06 PM |
Populate Content Control Dropdowns from Excel | Deirdre Kelly | Word VBA | 23 | 09-07-2017 02:51 PM |
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data | Faldinio | Word VBA | 7 | 10-19-2014 06:03 AM |