Excel hyperlink changes to local path

Copper Contributor

Hello all, I have a problem with the path of hyperlinks in an excel file changing. I have a file which has a list of hyperlinks to different drawings, these drawings are stored on the company sharepoint. All the links have a path to the company sharepoint location. Multiple people use this one document that is stored on the sharepoint aswell. However sometimes all the links in the file are changed to a local paths. The link "https.<company>.sharepoint.<location of file>" is then changed to "file:\\C:\User.<location file> and with that the link becomes useless. 


I already found out that in the option - advanced - web options - files - checkbox "update links on save" is to be unchecked to prevent this from happening but I assume this is a setting that needs to be set for each user account, which makes this not a realistic way to prevent this problem from happening. 


What can I change to the Excel file to prevent the links from changing and staying on the path to the sharepoint files? 

3 Replies
If all the hyperlinks in the workbook go to the same SP document library, you could use a hyperlink base (Stored in the properties of the workbook so it travels with the worbook):
See: Set the base address for links in a workbook
Thank you, this might indeed be a solution to my problem. Having the base adress in the file will solve my problem. When I click the cell it will go to the desired file.
However a next problem it created. My code now does not recognize the hyperlink, for my code cell.hyperlink.count>0 condition is not met. The code now skips the cells with this new hyperlink in it.

Any suggestions on this?
Nevermind! Rookie mistake, I have it fixed.

thanks for your initial suggestion, think it will help me out!