Microsoft Office Forums - View Single Post - [Solved] VBA Macro issue: Adding Hyperlinks to six-digit numbers

Guessed · 10-07-2023, 12:45 AM

It is novel to create a variable for the find so I'm going to offer a more traditional method rather than try to figure out the nuances. It is also odd to dimension the variables repetitively inside a loop. Try this version of your code

Code:

Sub AddHyperlinksToTimeMarkers()
  Dim mtgID As String, aRng As Range
  Dim hrs As Integer, mins As Integer, secs As Integer, startTime As Long

  mtgID = InputBox("Enter meeting ID")     ' Ask for meeting ID
  
  ' Find all six-digit numbers (time markers)
  Set aRng = ActiveDocument.Range
  With aRng.Find
    .ClearFormatting
    .Text = "[0-9]{6}"
    .MatchWildcards = True
    ' Loop through each time marker and add hyperlink
    Do While .Execute
      ' Get hours, minutes, and seconds from time marker
      hrs = CInt(Left(aRng.Text, 2))
      mins = CInt(Mid(aRng.Text, 4, 2))
      secs = CInt(Right(aRng.Text, 2))
      startTime = hrs * 3600 + mins * 60 + secs      ' Calculate starting time
      ' Add hyperlink to time marker
      ActiveDocument.Hyperlinks.Add Anchor:=aRng, Address:="https://ABC?meetingid=" & mtgID & "&start=" & startTime
      aRng.Collapse Direction:=wdCollapseEnd
    Loop
  End With
End Sub