VBA Macro issue: Adding Hyperlinks to six-digit numbers

yanyan9896 · #1 10-06-2023, 09:11 AM

I am looking for some assistance with editing my VBA code. I need a macro that can help me add hyperlinks to time markers in my document.

Here are the steps that the macro needs to accomplish:

1.Ask for meeting ID and allow user input through an inputbox.
2.Find all six-digit numbers (time markers) using the Mid function.
3.Convert the hours, minutes, and seconds obtained from the time markers to integers using the CInt function.
4.Calculate the starting time by multiplying hours by 3600, minutes by 60, and adding them together.
5.Loop through each time marker and add a hyperlink using the ActiveDocument.Hyperlinks.Add code, with the meeting ID and starting time as parameters.

Here is the code for the macro:

Code:

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

Guessed · #2 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 · #3 10-07-2023, 03:14 AM

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

yanyan9896 · #4 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!

Guessed · #5 10-08-2023, 03:22 AM

I think the issue is on the mins line. That is one line that I didn't check on your provided code and it appears to start from the wrong spot. Change the 4 to 3 eg

Code:

mins = CInt(Mid(aRng.Text, 3, 2))

You can debug these things yourself by stepping through the code and hovering over the variables after the line has executed to see what value they have.

yanyan9896 · #6 10-08-2023, 04:19 AM

Quote:

Originally Posted by Guessed

I think the issue is on the mins line. That is one line that I didn't check on your provided code and it appears to start from the wrong spot. Change the 4 to 3 eg

Code:

mins = CInt(Mid(aRng.Text, 3, 2))

You can debug these things yourself by stepping through the code and hovering over the variables after the line has executed to see what value they have.

Thank you for pointing out the issue with the "mins" line in the provided code. I apologize for not checking it thoroughly. I appreciate your suggestion regarding debugging. Stepping through the code and examining the variable values after execution will indeed help identify and resolve such issues in the future.

Once again, thank you for your keen observation and valuable input. I truly appreciate your assistance.

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