Replacing Hyperlinks in an Entire Workbook

For some reason my workbook that contains links hyperlinks to a networked drive at times reverts those inks to my own appdata location. I can't figure out how that happens, but I'd like to mass change them back to the correct network location. I found the following VBA code but it's not working. When testing the variable hlink.address always looks to be blank. Can someone tell me what's wrong with this code?

Sub ResetHyperlinks()
    Dim hLink As Hyperlink
    Dim wSheet As Worksheet

    For Each wSheet In Worksheets
       For Each hLink In wSheet.Hyperlinks
            hLink.Address = Replace(hLink.Address, "C:/users/j.myid\appdata\roaming\microsoft\excel", "\\fileshare\share\Excel Macros")
        Next hLink
    Next
End Sub

Originally Posted by CK76

Is your Excel version still 2003? Or has it changed?

If newer, likely culprit is Hyperlink base setting in the file.
Attachment 549552

If it is blank like above, set it to some non-existent location Ex: "\\ServerX\X\X\"

This will force the file to use absolute link for all hyperlinks and will not change based on workbook location.

Alternately, instead of using regular hyperlinks. You could use HYPERLINK formula to construct link.

Looking at code, maybe issue is with "/" mixed with "\". It's usually one or the other.

My Excel version is 2013, and the Hyperlink Base is set to blank. If I set it to something none existent, what'll that do? Also, the same question applies to using a Hyperlink formula vs. just adding and editing a hyperlink. Is the purpose of doing that either of these so that it won't ever change again back to my own appdata location and maintain whatever I've entered?

Originally Posted by DarkF1ame

js0873,

I constructed a little test and was able to get the address to change on my hyperlink using your code. I did obviously have to mess with the paths to make them specific to me but that shouldn't be a huge deal for you to fix. I would suggest finding where it's pointing now and pasting that string of text into windows explorer to make sure the link is properly formatted. When I stepped through the code (F8) the hLink.Address populated properly, double check the actual address that the hyperlink is pointing to (not just what is displayed) and verify it is entered correctly in the code. Secondarily I agree that the HYPERLINK function in Excel may be an easier way to construct your spreadsheet.

Hope this is helpful, if not please post again and we'll try to help.

DarkF1ame

I found my problem. The replace function had a typo, but the main thing with testing and seeing nothing was that I thought that this was only processing cells that had a hyperlink assigned ...

For Each hLink In wSheet.Hyperlinks

Instead it's processing all cells. I didn't realize that.

By setting it to something non-existent, it will force hyperlink to be absolute link at all times.
I.E. will always have full file path.
Ex: \\Servername\folder\file.xlsx instead of somthing like .\folder\file.xlsx. Which is relative link. Based on workbook location.

Correct, by using absolute link either by above method or using Hyperlink function, Excel will always use network drive location and will not adjust link path to your local folder.