Create macro to make text into hyperlink concatenating the display text to standard format

2
votes

I have to create a large number of hyperlinks for ticket items where the only part of the URL that changes is the ticket ID. I am new to this and much of the VBA help is for excel. My problem exists in MS-word and its difficult to transfer the syntax.

I expect the user to invoke the macro after entering a 6 digit Ticket ID. Ideally the macro would automatically select the last word typed by the user, and append it to the url segment which never changes.

I attempted to record a macro that copies the last word typed then concatenates it onto the end of the standard URL.

I can successfully do everything with keyboard shortcuts so I thought I would be set. The problem is the macro just uses the text from the recording example. It also turns my text to display to the example text.

Turning any text into the link I used as an example in the recording.

I can't figure out how to make VBA use the copied text for the text to display and the last 6 digits of the URL.

Please see VBA syntax below 

Sub textToHyperlink()
'
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
    Selection.Copy
    ChangeFileOpenDirectory _
        "/Users/chris/Library/Containers/com.microsoft.Word/Data/Library/Preferences/AutoRecovery/"
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
        "https://someboringwebsite.com/WorkItem&id=123456", SubAddress:="", ScreenTip:="", _
        TextToDisplay:="123456"

End Sub
2
vbahyperlinkms-word

2 Answers

1
votes

This may work:

Sub textToHyperlink()
'
    Dim TicketID As String
    TicketID = Selection.Range.Text 
    'You may need to clean up TicketID (remove <CR> etc.) before 
    'sticking it in the hyperlink. Depends on what's in Selection.

    ' Not sure what this line does
    ' ChangeFileOpenDirectory _
        "/Users/chris/Library/Containers/com.microsoft.Word/Data/Library/Preferences/AutoRecovery/"
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
        "https://someboringwebsite.com/WorkItem&id=" & TicketID, SubAddress:="", ScreenTip:="", _
        TextToDisplay:=TicketID

End Sub

Hope that helps.

1
votes

The following is in response to your clarification in a comment (which I've incorporated into your question). There are two variations

1) Following your premise, working with user input. Note that this will take the preceding word, no matter where it is in the text. And in the case the user may have typed a space (or many spaces) all spaces are removed from the end of that word. (These lines of code are commented out.)

2) You (and the user) might find it simpler to work with an InputBox so that the user can type the ticket id directly - no worries about where it is in the text, or spaces, or anything. The hyperlink is inserted at the selection.

Sub textToHyperlink()
'
    Dim sID As String
'    Dim rng As word.Range
'
'    Set rng = Selection.Range
'    rng.MoveStart wdWord, -1
'    sID = Trim(rng.Text)
    sID = InputBox("Please enter the ticket ID")
    ActiveDocument.Hyperlinks.Add anchor:=rng, Address:= _
        "https://someboringwebsite.com/WorkItem&id=" & sID, SubAddress:="", ScreenTip:="", _
        TextToDisplay:=sID

End Sub