Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-11-2022, 02:14 PM
DMSI DMSI is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Novice
Add content control to all words from an Excel list in a Word document
 
Join Date: Aug 2022
Posts: 4
DMSI is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 08-11-2022, 05:23 PM
Guessed's Avatar
Guessed Guessed is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 08-12-2022, 07:38 AM
DMSI DMSI is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Novice
Add content control to all words from an Excel list in a Word document
 
Join Date: Aug 2022
Posts: 4
DMSI is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 08-14-2022, 12:45 AM
macropod's Avatar
macropod macropod is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by DMSI View Post
So let's say [object1] is found in the text above it would apply a content control to it and move on
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]
Reply With Quote
  #5  
Old 08-14-2022, 04:22 PM
Guessed's Avatar
Guessed Guessed is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #6  
Old 08-15-2022, 12:50 PM
DMSI DMSI is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Novice
Add content control to all words from an Excel list in a Word document
 
Join Date: Aug 2022
Posts: 4
DMSI is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 08-15-2022, 04:45 PM
Guessed's Avatar
Guessed Guessed is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #8  
Old 08-18-2022, 01:53 PM
DMSI DMSI is offline Add content control to all words from an Excel list in a Word document Windows 10 Add content control to all words from an Excel list in a Word document Office 2016
Novice
Add content control to all words from an Excel list in a Word document
 
Join Date: Aug 2022
Posts: 4
DMSI is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Add content control to all words from an Excel list in a Word document 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:09 AM.


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