SOLVED

Office / Excel 365 - use relative hyperlinks instead of OneDrive

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3068778%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EOffice%20%2F%20Excel%20365%20-%20use%20relative%20hyperlinks%20instead%20of%20OneDrive%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3068778%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI've%20recently%20been%20%22upgraded%22%20to%20M365%20on%20my%20work%20computer%2C%20and%20it's%20been%20....%20interesting....%20There's%20a%20lot%20of%20changes%2C%20a%20lot%20of%20things%20have%20moved%20around.%20I'm%20getting%20used%20to%20it%2C%20and%20finding%20out%20how%20to%20solve%20things%20that%20are%20new%2Fbugging%20me.%20However%2C%20I'm%20yet%20to%20find%20a%20solution%20to%20relative%20hyperlinks.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20one%20Excel%20Workbook%20which%20is%20my%20index%2Fsummary%20of%20a%20bunch%20of%20other%20spreadsheets%2Fdocs.%20The%20index%20doc%20shows%20me%20where%20the%20other%20information%20is%20stored%20and%20its%20status.%20I%20want%20to%20hyperlink%20to%20each%20of%20the%20child%20spreadsheets%2Fdocuments%2C%20which%20are%20in%20%3CU%3Esub-folders%26lt%3B%5C%2FU%26gt%3B%20of%20the%20folder%20my%20index%20workbook%20is%20in.%20E.g.%20there%20is%20one%20spreadsheet%20holding%20the%20data%20for%20each%20date.%20Previously%2C%20this%20would%20just%20link%20to%20%22%3CU%3E%3CFONT%20color%3D%22%5C%26quot%3B%233366FF%5C%26quot%3B%22%3E%5C%5C2022-01-21%5C%5Cdailydata.xlsx%26lt%3B%5C%2FFONT%26gt%3B%26lt%3B%5C%2FU%26gt%3B%22.%20If%20it%20was%20in%20a%20parallel%20folder%2C%20it%20would%20link%20to%20%22%3CFONT%20color%3D%22%5C%26quot%3B%233366FF%5C%26quot%3B%22%3E%3CU%3E..%5C%5CotherProject%5C%5Cdocs%5C%5Cmydoc.docx%26lt%3B%5C%2FU%26gt%3B%26lt%3B%5C%2FFONT%26gt%3B%22.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FU%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FU%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ENow%20I'm%20on%20M365%20and%20OneDrive%2C%20%3CFONT%20color%3D%22%5C%26quot%3B%23FF0000%5C%26quot%3B%22%3E%3CU%3E%3CSTRONG%3EEVERYTHING%26lt%3B%5C%2FSTRONG%26gt%3B%20%26lt%3B%5C%2FU%26gt%3B%26lt%3B%5C%2FFONT%26gt%3Bis%20linked%20to%20the%20online%20address%20for%20the%20OneDrive%20doc%2C%20e.g.%20a%20link%20to%20a%20file%20in%20the%20same%20folder%20as%20the%20spreadsheet%20I'm%20working%20on%2C%20instead%20of%20being%20%22%3CU%3E%3CFONT%20color%3D%22%5C%26quot%3B%233366FF%5C%26quot%3B%22%3ETheOtherSheet.xlsx%26lt%3B%5C%2FFONT%26gt%3B%26lt%3B%5C%2FU%26gt%3B%22%2C%20is%20instead%20linked%20to%20%22%3CU%3E%3CFONT%20color%3D%22%5C%26quot%3B%233366FF%5C%26quot%3B%22%3E%3CA%20href%3D%22%5C%26quot%3Bhttps%3A%2F%2Fmy-company.sharepoint.com%2Fpersonal%2Fmy_name_and_company%2FDocuments%2FDocuments%2FCustomers%2FThis%2520Customer%2FThis%2520Project%2FIssue%2520Tracker%2FIssue%252001%2FData%2FTheOtherSheet.xlsx%5C%26quot%3B%22%20target%3D%22%5C%26quot%3B_blank%5C%26quot%3B%22%20rel%3D%22%5C%26quot%3Bnofollow%20nofollow%20noopener%20noreferrer%22%20noopener%3D%22%22%20noreferrer%3D%22%22%3Ehttps%3A%2F%2Fmy-company.sharepoint.com%2Fpersonal%2Fmy_name_and_company%2FDocuments%2FDocuments%2FCustomers%2FThis%2520Customer%2FThis%2520Project%2FIssue%2520Tracker%2FIssue%252001%2FData%2FTheOtherSheet.xlsx%26lt%3B%5C%2FA%26gt%3B%26lt%3B%5C%2FFONT%26gt%3B%26lt%3B%5C%2FU%26gt%3B%22...!!!%26lt%3B%5C%2FP%26gt%3B%3C%2FA%3E%3C%2FFONT%3E%3C%2FU%3E%3C%2FFONT%3E%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EYes%2C%20the%20links%20still%20work%20and%20open%20the%20local%20file%20when%20I'm%20offline%20(critical!).%20However%2C%20it%20doesn't%20make%20any%20sense%2C%20and%20it%20makes%20the%20links%20harder%20to%20quickly%20check%20%2F%20understand%20visually.%20I%20haven't%20tested%20what%20would%20happen%20if%20I%20were%20to%20move%20a%20project%20folder%20containing%20relative%20references%20to%20sub-folders%3B%20I'm%20assuming%20that%20OneDrive%20will%20fix%20the%20links...%3F%20Sort%20of%20afraid%20to%20try.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20get%20the%20old%20style%20relative%20hyperlinks%20back%3F%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3068778%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

I've recently been "upgraded" to M365 on my work computer, and it's been .... interesting.... There's a lot of changes, a lot of things have moved around. I'm getting used to it, and finding out how to solve things that are new/bugging me. However, I'm yet to find a solution to relative hyperlinks. 

 

I have one Excel Workbook which is my index/summary of a bunch of other spreadsheets/docs. The index doc shows me where the other information is stored and its status. I want to hyperlink to each of the child spreadsheets/documents, which are in sub-folders of the folder my index workbook is in. E.g. there is one spreadsheet holding the data for each date. Previously, this would just link to "\2022-01-21\dailydata.xlsx". If it was in a parallel folder, it would link to "..\otherProject\docs\mydoc.docx". 

 

Now I'm on M365 and OneDrive, EVERYTHING is linked to the online address for the OneDrive doc, e.g. a link to a file in the same folder as the spreadsheet I'm working on, instead of being "TheOtherSheet.xlsx", is instead linked to "https://my-company.sharepoint.com/personal/my_name_and_company/Documents/Documents/Customers/This%20..."...!!!

 

Yes, the links still work and open the local file when I'm offline (critical!). However, it doesn't make any sense, and it makes the links harder to quickly check / understand visually. I haven't tested what would happen if I were to move a project folder containing relative references to sub-folders; I'm assuming that OneDrive will fix the links...? Sort of afraid to try.

 

Is there any way to get the old style relative hyperlinks back?

 

Update: I've just discovered that when I click on one of these hyperlinks to a file on my PC, it downloads it from the cloud, creating a new file in my downloads folder instead of opening the bloody file on my PC!!!!

2 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution
I got an answer on Stack OverFlow that looks like it is working:
https://stackoverflow.com/a/71576122/2317071

Configure OneDrive to turn OFF "Use Office application to sync Office files" in OneDrive Settings / Office

Perhaps some office Gurus can comment on any side effects of this setting, other that what's shown in the help.
I can't find a way to do it on the first try - adding the anchor pretty much always defaults to OneDrive - BUT if you include a line right after that says "ActiveCell.Hyperlinks(1).SubAddress = target" with "target" being a string to the cell you want to reference in the file you want to get to, it at least works for hyperlinks inside of your current document.