Change large number of hyperlink names

[kjwedder]

I have over 3,000 Hyperlinks that I would like to change the name to match names listed in another column. Example I have names listed in Colum A and corresponding hyperlinks in columns I and J (hyperlink 1; and hyperlink 2). I would like to change the hyperlink names in both Columns to simply reference a document name I chose followed by the corresponding name in column A. Is there a formula or macro I can run to do this quickly?

 

[gmhumphr]

I have over 3,000 Hyperlinks that I would like to change the name to match names listed in another column. Example I have names listed in Colum A and corresponding hyperlinks in columns I and J (hyperlink 1; and hyperlink 2). I would like to change the hyperlink names in both Columns to simply reference a document name I chose followed by the corresponding name in column A. Is there a formula or macro I can run to do this quickly?

Would need more information on exactly what value you have in A, how you would like to concatenate it with a doc number, whether you would want to change the address that it points to (path way) or the display text (what you see underlined and is clicked on). Please use the code below as a starting point. It uses the hyperlinks object (collection of all hyperlinks), goes into each one and returns the range object (location in excel where the hyperlink exists), takes the row of it to find the value in column A for that row, then acts upon the specific hyperlink object by changing the address (where it links you to) and the display name (what the user sees in the cell as underlined in blue).

You need to change the dname variable at the top of the code and change how it concatenates with the value in column A (I don't have enough info to guess this)

Sub hlink()
Dim h As Hyperlink
Dim dname As String
Dim colname As String


dname = "docname "


For Each h In ActiveSheet.Hyperlinks
    With h
        colname = Cells(.Range.Row, "A").Value 'pull the value from column A and the row the hyperlink is on
        .Address = dname & colname 'change the link to address to a combination of some document name and the value in column A
        .TextToDisplay = colname ' change the text that is displayed/clicked on to just the value from column A
    End With
Next h

End Sub