Consulting

Results 1 to 4 of 4

Thread: Issues about adding hyperlinks

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Exclamation Issues about adding hyperlinks

    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!

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Quote Originally Posted by June7 View Post
    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.

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    You still haven't attached files to post. Still requires download from GoogleDrive.

    Okay, so where is this code supposed to be?
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •