Fix Hyperlinks

[sspatriots]

Good evening,

I found the code below online that is supposed to change the hyperlink file paths to a selected range. Every time I use it, only the cell I select prior to selecting a range gets updated. Just trying to figure out what I'm missing here.

Sub ChangeHyperlinks()

Dim cell As Range

For Each cell In Selection

If cell.Hyperlinks.Count > 0 Then

cell.Hyperlinks(1).Address = Replace(cell.Hyperlinks(1).Address, "..\..\drives\rManufacturing\00-Commercial & Group 2 PO Archive\2023", "H:\Jobs\00 PO ARCHIVE\2023")

End If

Next cell

End Sub

 

[sspatriots]

This is a little strange. I removed that line as you recommended and the results were about the same as the previous post where I used the code from post #11. So I ran it again on the same range and it grabbed and changed all but on of the cells in the selected range shown in my previous post. "0016" was the only one that didn't work after running that code twice.

 

[Fluff]

Not sure why that is happening as the two codes are basically the same.

 

[jkpieterse]

Here's what I would do:
- Make sure the addresses (the location the link points to) are in cells as text
- Use the HYPERLINK function to dynamically create links form those text cells
Next time you have a location change, a simple Find & Replace on the text column should do it.

 

[sspatriots]

Here's what I would do:
- Make sure the addresses (the location the link points to) are in cells as text
- Use the HYPERLINK function to dynamically create links form those text cells
Next time you have a location change, a simple Find & Replace on the text column should do it.

If my cells are in a table, would that affect the behavior using this code?

 

[jkpieterse]

I'm not sure what you are asking. If the cells are in a table, all you do is "edit" the cell values that contain the hyperlink address, something like this:

    Dim cl As Range
    For Each cl In ActiveSheet.ListObjects(1).ListColumns("LinkAddress").DataBodyRange
        cl.Value = Replace(cl.Value, "old part of address", "new part of address")
    Next

 

[Georgiboy]

Maybe it would be better to loop through the Hyperlinks rather than the cell ranges, the below changed the hyperlink address's in blocks depending on how i had pasted them. It loops the blocks of hyperlinks rather than each cell within a range:

Sub ChangeHyperlinks2()
    Dim oldLink As String
    Dim newLink As String
    Dim mHL As Hyperlink
    
    oldLink = "..\..\drives\rManufacturing\00-Commercial & Group 2 PO Archive\2023"
    newLink = "H:\Jobs\00 PO ARCHIVE\2023"
    
    For Each mHL In Selection.Hyperlinks
        If mHL.Address = oldLink Then
            mHL.Range.Interior.Color = vbYellow
            mHL.Address = newLink
        End If
    Next mHL
End Sub

If you step through the above code on the file below, you can see the blocks it is changing the links for (Highlighted yellow)
HLinks.xlsm

 

[sspatriots]

Here's what I would do:
- Make sure the addresses (the location the link points to) are in cells as text
- Use the HYPERLINK function to dynamically create links form those text cells
Next time you have a location change, a simple Find & Replace on the text column should do it.

This is what I will do next. Thanks

 

[sspatriots]

Here's what I would do:
- Make sure the addresses (the location the link points to) are in cells as text
- Use the HYPERLINK function to dynamically create links form those text cells
Next time you have a location change, a simple Find & Replace on the text column should do it.

I've done most of what you recommended and almost to the finish line. They do not like the use of the "HYPERLINK" function formulas around here; therefore, I'm trying to take it one step further. I just need help interpreting what one line of the code is telling me. I understand that it is trying to extract the address string, but I'm not sure what mine needs to say for the following formula to extract the correct information. My hyperlink formulas are in column "T" (20). Below is my first formula in cell T3:


=IF(S3="","",HYPERLINK(S3,R3))

Where column "R" is the "friendly_name" list that corresponds to column "S" which are the file paths of each file I want linked. Everything works fine up to this point.



I need to understand what to do with this line of the code to make it work

address_string = Mid(current_range.Formula, 25, InStr(20, current_range.Formula, ",") - 23)

My code so far:

Sub convert_hyperlink_formula_to_hyperlink_cell()
Dim address_string As String, display_string As String
Dim current_range As Range

For Each current_range In Selection
    address_string = Mid(current_range.Formula, 25, InStr(20, current_range.Formula, ",") - 23)
    Debug.Print
    
    display_string = current_range.Value
    ActiveSheet.Hyperlinks.Add Anchor:=current_range, Address:=address_string, TextToDisplay:=display_string
Next current_range
End Sub

Thanks, SS

 

[jkpieterse]

I really, really do not understand the aversion against the HYPERLINK function. It is by far the most stable and maintenance-friendly way of having a hyperlink in Excel. Given that you implemented the hyperlink function I also fail to understand why you need that code?

 

[sspatriots]

I really, really do not understand the aversion against the HYPERLINK function. It is by far the most stable and maintenance-friendly way of having a hyperlink in Excel. Given that you implemented the hyperlink function I also fail to understand why you need that code?

I guess it is because they are constantly adding PO's to this list and they use the insert Link function to add their hyperlinks and don't want to fool with dropping a file path in the list when they add their PO's. So, they said, get rid of the formulas. The code above is something I had used in the past, but I don't quite understand what to do with that one line of code to make it convert to an link that looks like it was created using the insert Link function.


Regards, SS