PDA

View Full Version : Solved: Automating Hyperlinks



Alasbabylon
11-08-2007, 10:57 AM
Is there a way to take a hyperlink from one column and assign it to the text in another column? We have an automated report that lists document names in column B and a link to the document in column D. I need to have the link from D assigned to the title in B for each line in the spreadsheet so I can tranfer that data to a Word document and publish it as an htm file.

Marci

XLGibbs
11-08-2007, 05:02 PM
Sub Changelink()

Dim aRng As Range, c As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
Set aRng = Range("B2:B8") 'set to correct range
For Each c In aRng
If Not c = "" Then
With c.Hyperlinks
If .Count = 0 Then
.Add c, c.Offset(, 2).Text, , , c.Text
Else
.Delete
.Add c, c.Offset(, 2).Text, , , c.Text
End If
End With
End If
Next c
Application.EnableEvents = False
End Sub

This will do it. Just change the variable aRng to be your range of column B.

Macro and result attached.