Office / Excel 365 - use relative hyperlinks instead of OneDrive

[Critcho]

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 · Accepted 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.

[#3]

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.

[#4]

@Critcho 

I have been battling this problem, pdf hyperlinks going to MS Edge. My work around is to reference the linked document via C:\Users\{username}\{OneDrive name}\...

I get a 'Trust Centre' warning message, but this is a small inconvenience for my links to launce into Acrobat.

Hope this is useful.