Forum Discussion
Excel hyperlink changes to local path
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?
- 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
https://support.microsoft.com/en-us/office/work-with-links-in-excel-7fc80d8d-68f9-482f-ab01-584c44d72b3e- 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!
I am having the same problem (absolute file paths changed to relative paths) when I save my excel with hyperlinks (added through VBA for excel). This can create problems when I share this file with another user/ when I copy the hyperlinks to another file (I use VBA for excel for the copy).
I do not see any option in the Hyperlinks.Add method to "retain" the absolute path names.
I am using Excel 2019 on am MacBook Pro (M2) with the current/ latest version of the MacOS.
I do not see the options for setting a base address that you mentioned for Excel 2019 on the MacOS version. Also, I'd like to do that through VBA to reduce errors.
