VBA Macro issue: Adding Hyperlinks to six-digit numbers

Guessed · #1 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

yanyan9896 · #2 10-07-2023, 03:14 AM

Many thanks for your help! The code works well! You're brilliant!

yanyan9896 · #3 10-08-2023, 02:44 AM

Quote:

Originally Posted by Guessed

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

Many thanks for your help! It works but the time it calculated is not correct. May you please help me to figure out why......T^T Again, Thanks for your help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find/replace - remove a space in 4 digit numbers	lalywizz	Word	5	10-08-2021 09:27 AM
Trying to change the last digit in a column of 10 digit entries	arkansawyer16	Excel	10	04-06-2020 02:03 PM
Adding PreserveFormatOnUpdate to macro that updates all hyperlinks within a document	JellehFishh	Word VBA	5	03-17-2020 02:47 PM
Regex/wildcard search for dates with 2-digit and 4-digit years	Marrick13	Word VBA	2	01-29-2016 07:04 AM
A challenging digit by digit manipulation, rotate the digit in range of 0 to 9	laucn	Excel Programming	14	05-17-2015 12:12 PM