The issue is easy to reproduce. Just create a new workbook, point at a cell, press Ctrl+K to create/edit hyperlink, select Link to: Existing File or Web Page, pick a file somewhere on your C-drive and OK it. For now, all is well and the clicking the link goes to the right place.
Now save the workbook, close and reopen it and try clicking the link again. Oops! The path has been edited by Excel on the assumption that your workstation is linked to some corporate domain and everything that you access locally is in fact stored in your roaming profile and synched to some network drive. Now I don’t know if this happens in all possible circumstances but I do know two workarounds that work for me:
1) Rather than a straight link, use the =HYPERLINK(“full path and filename”) or just store the fullpath and file in one cell, e.g. K7 and use =HYPERLINK(K7) and Excel won’t remap your stuff.
2) If the file you are linking to is on the same drive as the Excel sheet linking to it, and that relationship won’t change, use relative paths. This works particularly well if the files you are linking to are either in the same directory as the sheet you are editing, or in sub-directories under it, which is often the case with me. To do this, when you hit Ctrl+K to add a hyperlink, edit the target path before clicking OK and put .. in front (meaning go to directory above) then specify path from that point on only.
If you are using functions that reference other workbooks then read http://support.microsoft.com/kb/328440 to see the rules Excel follows in modifying paths in order to avoid problems.
In excel 2010,<o:p></o:p>
Go to file tab- click options button second up from the bottom- then click on save button on the left of the menu. Notice the auto recovery section and checkboxs, by default excel 2010 saves the file to c:\ apps data. Simply change the location to the E:\g:\ect drive location of the stored file. <o:p></o:p>
There you go, good luck.<o:p></o:p>
Tom King Texas<o:p></o:p>
Ran into this same problem today when I copied to a new drive folder then copied back over to local drive.
As someone mentioned below… simply enter the base hyperlink. I can only assume the original hyperlinks are fine as long as you don’t change the file path. But if you enter in a hyperlink base path (advanced properties of the file) then all the hyperlinks will defer to that as the base path and navigate from there.
\\server\master\level1\filelocation.ext is where the original file was. After I copied back and forth from network to local drive the path went to ..\level1\filelocation.ext. I assumed I could add \\server\master\ and it would start there. Turns out the ..\ is taking the path back one level, so I had to enter \\server\master\level1\ for this to work.
Guys, I also deal with this issue, and – as soon as we don’t know what is the source – here’s a quick fix for all our broken links:
Sub RepairLinks() Dim hLink As Hyperlink For Each hLink In ActiveSheet.Hyperlinks hLink.Address = Replace(hLink.Address, “SOURCE”, “DESTINATION”) Next hLink End Sub
SOURCE = C:\Users\username\AppData\Roaming\Microsoft\Excel\
DESTINATION = \\SERVER\shared\
This macro will repair your links in the active sheet.
It can be resolved by turn off File “update Links on save”.
How to get there in excel 2010:
File > Options > Advanced > Scroll down to “General”, then Click “Web Options” > Files, then uncheck “update Links on save”.
Had same issue in Excel 2007. Solved!
In “Excel Options” go to “Advanced”.
There uncheck “Save external link values”.
Regards, Zvika Melamed
Thankyou for this macro.
Instead of using the replace function however, in my worksheet the text being displayed was also the hyperlink target, so I just used:
hLink.Address = hLink.TextToDisplay
In the for loop.
To prevent it from happening in the future I also used a lot of the solutions here to change the excel settings… hopefully one of them sticks.
The best way I’ve found to avoid this issue is to put the hyperlink location as text in a cell in the same workbook and then use the hyperlink command to point to the cell with an indirect command.
For example, in cell B20 in a spreadsheet named “Data” (or what ever your sheet name is) place the text:
( this is the relative path pointing to the file you want to link to)
(this is the full path pointing to the cell you want to link to)
in the cell that you want to click on and go to the hyperlinked address, place:
Even better if you want to create many links, you can make the row address 20 a variable:
where cell A1 contains the row number…this makes copy and pastes work if you’re doing a lot of hyperlinks…Cell A1 contains the number 20, Cell A2 contains the next number etc…
replace your Hyperlink you just created with the full network link to server ex:
the hyperlink you just created look like
replace it by :
just copy \\serverIPadress/ShareDriveLocation and paste in front of all your damaged hyperlink
Excel UNC linking and information