Hi community,
I would like to ask if it is possible to use word macros to add hyperlinks to same texts in different sequence. For example, I have a conversation content in a word document:
Speaker 1: Hello I am ABC
Speaker 2: Please sit down.
Speaker 1: OK
Speaker 2: Please introduce yourself in 1 minute.
Speaker 1: I am XXX, I graduated from XXX......
Different hyperlinks need to be added to "speaker 1" and "speaker 2". However, "speaker 1" and "speaker 2" happened multiple times in a word document. General mailmerge hyperlink word macro or addins cannot identify the sequence of them.
Is there any method to automatically add different hyperlinks to the same texts but in different sequence according to the excel file?
I tried to draft the code as follows:
Code:
Sub AddHyperlinks()
' Open the Excel file containing the hyperlinks
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\Path\To\Hyperlinks.xlsx")
' Loop through each occurrence of "Secretary" in the document
Dim doc As Document
Set doc = ActiveDocument
Dim rng As Range
Set rng = doc.Content
Dim counter As Integer
counter = 1
Do While rng.Find.Execute("Secretary", MatchWholeWord:=True)
' Add the hyperlink to this occurrence of "Secretary"
Dim hyperlink As String
hyperlink = xlWB.Sheets(1).Cells(counter, 1).Value
Dim linkRange As Range
Set linkRange = rng.Duplicate
linkRange.Collapse Direction:=wdCollapseStart
linkRange.MoveEndUntil Cset:=" "
rng.Hyperlinks.Add Anchor:=linkRange, Address:=hyperlink
counter = counter + 1
Loop
' Close the Excel file
xlWB.Close
xlApp.Quit
End Sub
Your help will be greatly appreciated.