Excel 365 - How do find/replace a part of the hyperlink

[SandsB]

Changed servers. Find/Replace doesn't see the text I need to change. What's the trick to doing a mass change (1,000's) of hyperlinks?

 

[bobsan42]

if it's not HYPERLINK() function, but actual HYPERLINKs - you need a VBA script.

 

[bobsan42]

maybe something like this - checks all cells in the active sheet:

Sub replaceHLinks()
    Const oldText As String = "place text to replace between the quotes"
    Const newText As String = "place NEW text between the quotes"
    
    Dim cc As Range
    
    Application.Calculation = xlCalculationManual
    For Each cc In ActiveSheet.UsedRange
        If cc.Hyperlinks.Count = 1 Then
            With cc.Hyperlinks(1)
                Debug.Print "cell " & cc.Address, .Address, "changed to: ",
                .Address = Replace(.Address, oldText, newText, , , vbTextCompare)
                Debug.Print .Address
            End With
        End If
    Next cc
    Application.Calculation = xlCalculationAutomatic
    Set cc = Nothing
End Sub

place your own values for oldText and newText in the code.

 

[SandsB]

This worked but - I need to use the drive letter for the new server instead os spelling out the full URL like I used to. So old text was "oldserver.coname.com" and new text is "J:\". J is a mapped OneDrive drive. The link works but it's pointing to MY version of that link instead of just J:\ which would work for everybody. It shows
\\localhost\c$\Users\MyUserID\OneDrive - CoName\Documents - ShareName\ where MyUserID is unique to me.

 

[bobsan42]

well, it sort of looks like J was a map for \\localhost\c$\...., which is odd
but if you know the server address and path you should use that rather than the map, for example: \\192.168.0.125\shared\....

 

[SandsB]

The personalized link changed to the drive letter when I saved the file. Like when you link to a file that you have open Excel displays it differently - Same for this onedrive link. Your macro did the trick - thanks

 

[SandsB]

Mostly worked. A lot of links have spaces in the folder or filenames. The conversion left them with a lot of %20's for the spaces. I tried this macro replacing "%20" with " " but the change doesn't happen. Is there something special going on in a link? Some other way I should specify the %20 to get them all to change by "edit Link"? It's definitely the %20 because when I manually change them to a space it works fine. Changing 1,000's of link manually will take too long.

 

[bobsan42]

is %20 in the displayed text or in the link address?
if it's in the displayed text - you can use the Find/Replace dialog
or use the same code but replace .Address with .TextToDisplay

if it is in the address - is it a problem? The code should work for such changes as well.

However, .Address with .TextToDisplay can be different, so changing one of them doesn't change the other.

 

[SandsB]

It's not displayed. bobsan42's macro works great except the spaces are replaced with %20. I can click Edit Link and delete the space and then the links work. But - the macro doesn't change them. I'm at a loss.

 

[bobsan42]

this seems to happen with relative addresses, but not with absolute ones. May also happen with cloud connected folders.
However, this should not break the link functionality.
I suspect something else: since you renamed from web address to local address, iirc, maybe you also have to replace / with \
Maybe there is a mix of the two in your new links. Just guessing