Thread: [Solved] Word Macro: How to automatically add hyperlinks to timestamp texts?
View Single Post
 
Old 02-12-2023, 09:22 AM
loklokpanda loklokpanda is offline Windows 10 Office 2019
Novice
  
Join Date: Feb 2023
Posts: 1
loklokpanda is on a distinguished road
Default Word Macro: How to automatically add hyperlinks to timestamp texts?
Hi community, Is it possible to automatically add hyperlinks to timestamp texts?

I have a document, which contains a table with 4 columns. The first columns had already filled in timestamps. E.g., 000000 to 000120. And I have a private video uploaded to youtube, which is a recorded meeting. How to write the macro that can automatically add hyperlinks to the timestamps texts?

For example, speaker 1 made his/her speeches from 00:00 to 01:35, and speaker 2 made his/her speeches from 01:36 to 02:00. The youtube URL had already been inserted in the Macro, then it first read the timestamp texts at each cell, and add "&t=_m_s" in that youtube URL according to the timestamp it read.

The below macro can automatically add hyperlinks to text "[1]", "[2]", "[3]" etc. But it cannot add hyperlinks according to the timestamp texts:

Code:
Sub HyperlinkReferences()

Application.ScreenUpdating = False
Dim ReferenceSection As String
Dim rng As Range

ReferenceSection = ""

Dim ref(1 To 3) As String
ref(1) = ""
ref(2) = ""
ref(3) = ""

For i = 1 To UBound(ref) 'i = 1 to number of references
    Selection.HomeKey wdStory
    
    Selection.Find.ClearFormatting
    With Selection.Find
        If ReferenceSection <> "" Then
            .Text = "[" & ReferenceSection & "." & i & "]"
        Else
            .Text = "[" & i & "]"
        End If
        '.Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = False
    End With
    
    While Selection.Find.Execute
        If Selection.Hyperlinks.Count > 0 Then
            Selection.Hyperlinks(1).Delete
        End If
        If ref(i) <> "" Then
            Set rng = Selection.Range
            rng.SetRange Start:=rng.Start + 1, End:=rng.End - 1
            Selection.Hyperlinks.Add Anchor:=rng, _
            TextToDisplay:=Mid(Selection.Range.Text, 2, Len(Selection.Range.Text) - 2), _
            Address:=ref(i)
        End If
    Wend
Next i

Application.ScreenUpdating = True
End Sub
Since we can link to a specific part in a YouTube video by editing the URL like this:

(Using "The Phantom of the Opera - Emmy Rossum, Gerard Butler" as an example)

URL start from 00:00 - https://www.youtube.com/watch?v=pgz6PnHkmpY
URL specifically starts at 1:30 - https://www.youtube.com/watch?v=pgz6PnHkmpY&t=1m30s

If I typed Gerard Butler as speaker 10, he sings "inside my mind" at 0130. So one of the cells in the first column filled in 0130. The expected macro can read "0130" and add "t=1m30s" after the URL, then add this hyperlink to the "0130" timestamp texts in the document.

If the above cannot be done by macro, is it possible that I type in all timestamp texts in an Excel file first (e.g., 0130), then change the cell's format shown as "h"m"mm"s", then add &t=before the timestamp texts. Add all these to the URL in Excel. Then all specific time URL is completed. Thereby using a macro to add hyperlinks according to this excel?

Your help would be greatly appreciated.
Reply With Quote