Macro for Word or Excel that changes hyperlink display text to address

[susan.fineman]

I have over 1000 citations for which the article titles are the display
text for the hyperlinks. For each article I want to change the display
text to the hyperlink address. I tried recording a new macro, but it
wouldn't record the right-mouse "edit hyperlink" selection. I couldn't
find a keystroke alternative for selecting edit hyperlink. I am new to
writing macros in VBA but different postings imply that this is the way
to automate my task. Can someone please tell me how to write a macro
to accomplish this task?

 

[Helmut Weber]

Hi Susan,

maybe the limitations of this aren't relevant for your:

Dim oHpl As Hyperlink
For Each oHpl In ActiveDocument.Hyperlinks
oHpl.TextToDisplay = oHpl.Address
Next


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[StephanieH]

This is one that I use a lot. You can alter it for your needs.

Sub HyperChangePlot()

Dim oldtext As String
Dim newtext As String
Dim h As Hyperlink


oldtext = "Gross Plotting by Product"
newtext = "Gross Placement Plotting"


For Each h In ActiveSheet.Hyperlinks
x = InStr(1, h.Address, oldtext)
If x > 0 Then
If h.TextToDisplay = h.Address Then
h.TextToDisplay = newtext
End If
h.Address = Application.WorksheetFunction. _
Substitute(h.Address, oldtext, newtext)
End If
Next
End Sub