Issues about adding hyperlinks

[karenahahaha]

Hi everone,

I would like to seek help about editing the following macro.

(Prior to conducting my research, I sought advice from experienced VBA experts who suggested a solution. According to their recommendation, I can incorporate a numbered bookmark for each item found. This approach enables the retrieval of the calculated start time in seconds, which can then be assigned back to the corresponding hyperlinks associated with each bookmark. But I am still new to VBA and I am not fully understand that.)

I aim the macro can perform the following tasks:

1. Using the following code to browse and choose the Excel file (the Excel file contains column A (text) and column B (6-digit numbers)

Dim EXL As Object
Dim xlsWB As Object
Dim xlsPath As String

Set EXL = CreateObject("Excel.Application")

    xlsPath = BrowseForFile("Select Workbook", True)
    If Not xlsPath = vbNullString Then
        Set xlsWB = EXL.Workbooks.Open(xlsPath)

Private Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
Dim fDialog As FileDialog
    On Error GoTo ERR_HANDLER
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .Filters.Clear
        If bExcel Then
            .Filters.add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
        Else
            .Filters.add "Word documents", "*.doc,*.docx,*.docm"
        End If
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo ERR_HANDLER:
        BrowseForFile = fDialog.SelectedItems.Item(1)
    End With
lbl_Exit:
    Exit Function
ERR_HANDLER:
    BrowseForFile = vbNullString
    Resume lbl_Exit
End Function

2. Extract 6-digit numbers from selected Excel file's column B into the macro

'I don't know how to write this part. Here's the code for Extracting 6-digit numbers from Word for your references.

With aRng.Find
    .ClearFormatting
    .Text = "[0-9]{6}"
    .MatchWildcards = True

3. Use the following code to calculate start time

Dim Hr As Integer
  Dim Mn As Integer
  Dim Sc As Integer
  Dim startTime As Long

    Do While .Execute
      ' Get hours, minutes, and seconds from time marker
      hrs = CInt(Left(aRng.Text, 2))
      mins = CInt(Mid(aRng.Text, 3, 2))
      secs = CInt(Right(aRng.Text, 2))
      startTime = hrs * 3600 + mins * 60 + secs

4. Enter the meeting ID for merging the link

Dim mtgID As String

mtgID = InputBox

If mtgID = Empty Then
Exit Sub

5. Add hyperlinks to the names according to selected Excel file's column A

ActiveDocument.Hyperlinks.Add Anchor:=aRng, Address:="https://ABC?meetingid=" & mtgID & "&start=" & startTime
      aRng.Collapse Direction:=wdCollapseEnd
    Loop
  End With
End Sub

Those names' format are as follows:

With Selection.Find
        .Font.Name = "Times New Roman"
        .Font.Size = 14
        .Font.BOLD = True
        .MatchWildcards = True`

Here are the demo files for testing:

1. Word Document (contain Names) https://docs.google.com/document/d/1...f=true&sd=true
2. Excel file (contain column A(Names) and column B (6-digit numbers) https://docs.google.com/spreadsheets...f=true&sd=true
   
   I would like to express my gratitude in advance to anyone who can provide assistance in editing the macro. Thank you!

[June7]

I don't find any code in the Word document. How is this supposed to work?

Many will not download files from external location. Better to attach files directly to post. Can use Windows Compression and zip to one file for attachment.

[karenahahaha]

I don't find any code in the Word document. How is this supposed to work?

Many will not download files from external location. Better to attach files directly to post. Can use Windows Compression and zip to one file for attachment.

I am sorry for not uploading the files in the post.

There is no code in that Word Document.

What I want to achieve is to use a Word macro to add hyperlinks according to an Excel file:

Step 1. allow users to browse and choose an Excel file (that Excel file contains column A (text) and column B (6-digit numbers))

strWB = BrowseForFile("Select Workbook", True)
strSheet = "URL"
If strWB = Empty Then
Exit Sub
Else

Private Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
Dim fDialog As FileDialog
    On Error GoTo ERR_HANDLER
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .Filters.Clear
        If bExcel Then
            .Filters.add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
        Else
            .Filters.add "Word documents", "*.doc,*.docx,*.docm"
        End If
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo ERR_HANDLER:
        BrowseForFile = fDialog.SelectedItems.item(1)
    End With
lbl_Exit:
    Exit Function
ERR_HANDLER:
    BrowseForFile = vbNullString
    Resume lbl_Exit
End Function

2. Ask the user to input "Meeting ID" in the input box, e.g.:

  Dim mtgID As String
mtgID = InputBox("Input meeting ID。")
  If mtgID = Empty Then
    MsgBox "No meeting ID input。"
    Exit Sub
    Else

3. Find all 6-digit numbers (extract the column B of the selected Excel file)


I just know the code for Word, but i don't know how to edit it for Excel:

Dim aRng As Range
Set aRng = ActiveDocument.Range
  With aRng.Find
    .ClearFormatting
    .Text = "[0-9]{6}"
    .MatchWildcards = True

4. Calculate the start time

  Dim Hr As Integer
  Dim Mn As Integer
  Dim Sc As Integer
  Dim startTime As Long
 Do While .Execute
      Hr = CInt(Left(aRng.Text, 2))
      Mn = CInt(Mid(aRng.Text, 3, 2))
      Sc = CInt(Right(aRng.Text, 2))
      startTime = Hr * 3600 + Mn * 60 + Sc
 Loop

5. Add hyperlinks to the text in a Word Document, according to the selected Excel file

E.g.:

ActiveDocument.Hyperlinks.add Anchor:=aRng, Address:="https://ABC?meetingid=" & _
 mtgID & "&start=" & startTime
aRng.Collapse Direction:=wdCollapseEnd

Here's the original macro for using in Word Document:

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

Here are files for testing:
https://drive.google.com/drive/folde...i_?usp=sharing

My boss has requested me to add hyperlinks manually to more than 100 documents. May you please help me to solve the above issue? Many thanks in advance for your help! T^T (attached sample files for testing)


Thank you for your time and expertise, and I look forward to hearing from you soon.

[June7]

You still haven't attached files to post. Still requires download from GoogleDrive.

Okay, so where is this code supposed to be?