#1
|
|||
|
|||
Add content control to all words from an Excel list in a Word document
Hello pretty new to VBA, been searching for a method to scan through an excel list and apply Content Controls to any words in that list. I found a similar thread on this site but it looks like it goes Word to Excel so I just need to try and build it the other way.
I currently have put together a macro from some I've found online which scans a document if it is in single or double brackets depending how I change the regex statement. Anyway wondering it would be possible to scan an excel list of 64 items and then scan the word document for those items and apply content controls if they exist. Current macro I have: Single Bracket content control Sub ReplaceTags() ' ' ReplaceTags Macro ' ' Dim myRange Set myRange = Application.ActiveDocument.Content Dim myFind Set myFind = myRange.Find With Selection.Find .ClearFormatting .Text = "(\[)(*)(\])" .Execute Forward:=True .MatchWildcards = True End With If Selection.Find.Found = True Then Selection.Range.ContentControls.Add (wdContentControlText) Selection.ParentContentControl.Tag = Selection.Text End If End Sub Sub ReplaceAllTags() ' ' ReplaceAllTags Macro ' ' For i = 0 To ActiveDocument.Words.Count Selection.EscapeKey Application.Run MacroName:="ReplaceTags" Next End Sub Now if anyone can point me to how to point this to an excel list and scan than then pass it through to the above functions that would be much appreciated. |
#2
|
||||
|
||||
Can you explain the reason you want to do this and provide a sample Word and Excel file to do testing on?
At the moment it sounds like a vba training exercise.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Hi thanks for responding.
So what we are looking to do is scan through a document and any time the specified word in the excel is found it would add a content control box to it, we are using the content control as a way to replace variables in our docs. I have to use dummy text as I don't think it would be appropriate for me to upload our actual content. Example excel list: In Column A [object1] [object2] [object3] [object4] [object5] Dummy text from one page of the word doc: ● Lorem ipsum dolor sit amet, sit ex conceptam constituto. Ea nec omittam partiendo. Nisl [object1]-pertinax et mea, virtute aliquam ad mei. In per assum iudicabit, sea in eros movet, ex postulant [object2] vel. At altera tritani laoreet vim, has malorum accommodare et. [object3] docendi imperdiet at his. Duo inani nullam [object3] et, ad cum soluta utroque [object5], viris graece inciderint qui te. So let's say [object1] is found in the text above it would apply a content control to it and move on, so I think it would have to pass a variable to the ReplaceTags or ReplaceAllTags. I am just not sure how to scan an excel list and pass it through. Any guidance would be appreciated, I'll keep trying and hunting in the mean time and post any updates I have. So I can't upload anything off my current network but I can create a sample and upload later today or tomorrow. I was trying to work of this previous post: https://www.msofficeforums.com/word-...ord-excel.html But I think it is solving a different scenario. thanks |
#4
|
||||
|
||||
What kind of content control would be used, what title or tag, if any, would be applied to it, and what would it display? And why is a simple Find/Replace using an Excel list not sufficient?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
I would be using Mapped Plain Text Content Controls to do this and the user could import that data from Excel as an XML file. This can be automated with a macro to replace the xml file (or set its values) and avoid working with the text via find and replace.
Your Excel data needs to be in fieldname/value pairs. In Excel this could be a second column eg. object1...Joe object2...Smith object3...123 Main St object4...Springfield Your original question would enable you to insert all the necessary Content Controls but would need to include either setting the Title/Tag properties to the fieldname or setting the mapping. Alternatively, if you are using a find/replace method recommended by Paul, then you would need to make sure you search all stories to get entries in the headers/footers etc.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#6
|
|||
|
|||
Thanks Paul and Andrew.
I think the mapping you mentioned would be ideal once we have the words tagged as plain text content control objects. So the Title would be the Object name, and the Tag would be DocumentVariable always. Circling back to my original questions is it possible to scan the document for the words from an excel list? Let's say I have 10 documents with all the same possible variables that would to be converted to Content Control Objects. I found a post on how someone built a find and replace based of an excel list: Sub Main() Dim xl as Object 'Excel.Application Dim wb as Object 'Excel.Workbook Dim ws as Object 'Excel.Worksheet Dim rng as Object 'Excel.Range Dim cl as Object 'Excel.Range Set xl = CreateObject("Excel.Application") Set wb = xl.Workbooks.Open("c:\folder\file.xlsx") '## Modify as needed Set ws = wb.Sheets(1) '##Modify as needed Set rng = ws.Range("A1", ws.Range("A1").End(xlDown)) For each cl in rng Call Macro5(cl.Value, cl.offset(0,1).Value) Next End Sub Sub Macro5(findText$, replaceText$) ' ' Macro5 Macro ' ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = findText .Replacement.Text = replaceText .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.Execute End Sub Original post here: vba - "Find and Replace" multiple words in Word from Excel list - Stack Overflow Would it be possible to change that to apply the content control instead of the word replacement? Maybe something like this? if Macro5 above was changed out. Sub ReplaceTags(findText) With Selection.Find .ClearFormatting .Text = findText .Forward:=True .Wrap = wdFindContinue .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Range.ContentControls.Add (wdContentControlText) Selection.ParentContentControl.Title = Selection.Text Selection.ParentContentControl.Title = "DocumentVariable" End Sub |
#7
|
||||
|
||||
Post sample docs and excel source file...
The CC Range should be the selection text The CC Title should be the name of the field The CC Tag can be anything but I would make it a value that indicates it is mapped The mapping should be to the xml element with the name of the field It is most efficient to do the creation of the CCs and the mapping all at the same time.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#8
|
|||
|
|||
hey so I figured out a solution that worked for me. Basically what I did was combine both the codes I posted below, first my macro scans the doc and does a word replace to put the word in double brackets, then applies the content control and then runs the word replace again to remove the double brackets.
Here is a sample of what I used incase anyone needs to solve for a similar for something similar with lots of words that need to have content controls added in longer word documents. Sub Add_Content_Controls() 'This runs a series of macros to add DocumentVariables within a Word document Call Content_Control_AddBrackets Call AddAllTagsDouble Call Content_Control_RemoveDoubleBracket MsgBox "Content Controls Added" End Sub Function AddAllTagsDouble() ' ' Runs add content control in entire document ' ' For i = 0 To ActiveDocument.Words.Count Selection.EscapeKey Call AddTagsDouble Next End Function Function AddTagsDouble() ' ' Adds Conent Control to objects within a double bracket ' ' Dim myRange Set myRange = Application.ActiveDocument.Content With Selection.Find .ClearFormatting .Text = "(\[{2})(*)(\]{2})" .Execute Forward:=True .MatchWildcards = True End With nobrackets = Replace(Replace(Selection.Text, "[", ""), "]", "") If Selection.Find.Found = True Then Selection.Range.ContentControls.Add (wdContentControlText) Selection.ParentContentControl.Title = nobrackets Selection.ParentContentControl.Tag = "DocumentVariable" End If End Function Sub Content_Control_AddBracket() ' Adds brackets to all text listed in Variable list Dim wd As Word.Application Dim xl As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim rng2 As Excel.Range Dim cl As Object Dim Counter As Integer StatusBar = "Scanning document for DocumentVariables, please wait =" 'If ActiveDocument.TrackRevisions = False Then ' ActiveDocument.TrackRevisions = True 'End If Set xl = CreateObject("Excel.Application") Set wb = xl.Workbooks.Open("YOUR EXCEL DOCUMENT") '## Modify as needed Set ws = wb.Sheets("Custom") '## Modify as needed Set rng2 = ws.Range("A1", ws.Range("A1").End(xlDown)) For Each cl In rng2 Call FindReplace(cl.Value, cl.Offset(0, 1).Value) Next wb.Close xl.Quit End Sub Sub Content_Control_RemoveDoubleBracket() Dim wd As Word.Application Dim xl As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim rng2 As Excel.Range Dim cl As Object Dim Counter As Integer StatusBar = "Cleaning DocumentVariables, please wait =" 'If ActiveDocument.TrackRevisions = False Then ' ActiveDocument.TrackRevisions = True 'End If Set xl = CreateObject("Excel.Application") Set wb = xl.Workbooks.Open("YOUR EXCEL DOCUMENT") '## Modify as needed Set ws = wb.Sheets("Custom") '## Modify as needed Set rng2 = ws.Range("A1", ws.Range("A1").End(xlDown)) For Each cl In rng2 Call FindReplace(cl.Value, cl.Offset(0, 1).Value) Next wb.Close xl.Quit End Sub Function FindReplace(findText, replaceText) As Integer ' With Selection.Find .Text = findText .Replacement.Text = replaceText .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = True .MatchWholeWord = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With bReplaced = Selection.Find.Execute(Replace:=wdReplaceAll) If bReplaced = True Then FindReplace = 1 Else FindReplace = 0 End Function There might be a better more efficient way to do this, but it worked for what I needed. thanks all. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Import list from Excel into drop-down content control in word | shanshan89 | Word VBA | 10 | 08-11-2022 11:53 PM |
Populate Word content control fields with data from Excel | Kapluke | Word VBA | 8 | 01-10-2022 05:39 AM |
Push Word content control data and excel cells | shaztastic | Word VBA | 13 | 08-27-2018 06:46 AM |
Combo Box Content Control: How to update the list throughout the whole document? | deepak_fer | Word | 9 | 03-18-2018 04:10 AM |
Word 2010 Content Control help - Combo Boxes vs Drop Down List | proghy | Word | 1 | 09-16-2014 02:01 PM |