Use VBA to replace RegEx strings with hyperlinks in Word

Question

I'm trying to search through a word document and replace regex matches with a series of static hyperlinks.

For example, if I get a regex match for "A-1", I want to replace the "A-1" string with a hyperlink with Anchor = "A-1" and Address = "https://www.my_website.com/A-1". My RegEx matches could be "A-1", "A-2", "A-3", etc.

I'm familiar with RegEx but I'm very new to VBA. What I have so far:

Sub FindAndHyperlink()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")

    RegEx.Global = True
    RegEx.Pattern = "([A][\-])([0-9])"

    Set Matches = RegEx.Execute(ActiveDocument.Range.Text)
    For Each Match In Matches
        ActiveDocument.Range.Text = RegEx.Replace(ActiveDocument.Range.Text, (ActiveDocument.Hyperlinks.Add Anchor:=Match, Address:="https://www.my_website.com/" & Match))
    Next

End Sub

This doesn't compile because it's expecting a ) after ActiveDocument.Hyperlinks.Add.

I think the problem is that the RegEx.Replace() method is expecting (String, String) arguments rather than (String, Hyperlink object), but I'm not sure the best way to get around that.

Any help would be appreciated.

I don't think replace makes sense for what you're trying to do. I think it'd make more sense to clear the text at Match.Range and then instead add the hyperlink at the location of the Match.Range — Marcucciboy2
@Marcucciboy2 how would you do that? I'm thinking something like 'For Each Match In Matches' 'Match.Text = ""' ActiveDocument.Hyperlinks.Add Anchor:=Match, Address:="my_website.com" & Match' — UCanCallMeBob89
Does Matches actually receive a range from RegEx.Execute of each match for that regex? — Marcucciboy2
You cannot do that with regex alone, the regex only works on plain text. Find the index in the document and try to add a hyperlink at that position. — Wiktor Stribiżew

macropod macropod · Accepted Answer · 2018-10-18T07:02:02

Try:

Sub FindAndHyperlink()
Application.ScreenUpdating = False
Const HLnk As String = "https://www.my_website.com/"
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "A-[0-9]{1,}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    .Hyperlinks.Add Anchor:=.Duplicate, Address:=HLnk & .Text, TextToDisplay:=.Text
    .Start = .Hyperlinks(1).Range.End
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

Use VBA to replace RegEx strings with hyperlinks in Word

2 Answers