Add option to avoid OneDrive from changing Excel references to other files
Everytime you link an Excel workbook to other workbook, OneDrive changes the reference to the file, making reference to an online files. However, if one the Owrkbooks are in differente folders inside OneDrive, the link will break, even if you try to open the file in the computer containing them. That is a HUGE problem
Thanks, your solution works but only if we do not update the links later... The problem remains and Microsoft is very disappointing
Hey Guys, I spent a few hours on this and realize lots of people have this problem. I got mine fixed. I am on Mac running o365.
The problem was that when I open the file, excel complains a broken link points to a OneDrive path that seems to be invalid as it looks like my local path. Not sure how I got to that. All I can think of is may be because I have been working on local files and Save As to OneDrive to backup my working versions.
So, back to the solution, I first tried to do a Edit->Find on link name on all formulas, content, etc.. Didn't find anything.
I then create a new blank workbook, from the original workbook, I select all sheets from sheets tab, do a Move / Copy..., and select the blank work book. All sheets copied over.
I then save and reopen the new workbook. Found out the problems stick. I then went ahead and delete one sheet at a time. Each time, I save, close, and re-open to see when the problem goes away.
I finally got to one sheet that caused the problem.
After spending some time, I find out the problem is related to Data Validation. I have cells that use Data Validation to create a drop down list. I guess it must have pointed to remote file. Unfortunately Data Validation does not get searched as formulas.
I clear the cells, problem went away. I recreate the data validation again. Happy Day for me!
Lesson learn, I think if a link is created in features that's not in a formula, find the broken link is impossible. One can only narrow down to which sheet and then check Data Validation, Conditional Formatting, or any feature that can link to other sheets.
Hope that helps.
PS. Man, it's been 10 years since last time I used a spreadsheet. Gotta say, o365 makes me feel like it has only advanced 1 years compares to other solution out there that are growing a light speed. I think that's what happens when too much comfortable passive revenue from the existing user base makes business lazy to continuously improve.
Doug Scott commented
I no longer have this problem since I started syncing One Drive to my local hard drive.
This is a really annoying issue. Excel keeps on referencing an external URL but the rest of the URL is a location on my local drive. No way of removing it so I have to manually update every time. Please fix or I will go back to Box.com
This is still a big problem on O365 Mac Excel. When you re-open a file with links to a local source file, the links are automatically changed to a long hyperlinked file address. Excel says it can't find the file, then I have to go re-point the Data Source from Remote File to Local File. Very annoying and no options to fix on a mac.
Go to File > Options > Advanced > At general section select Web Options > Select Files tab and untick "Update links on save'.
However the formula that file path had already change, will have to change back manually.. if only i found out this before my formula changed to file path..
i tried two things: write 'file:///' before the address in the 'Insert Hyperlink'-popup. And another thing helped: Menu Data -> Edit Links -> Button 'Startup Prompt...' -> choose the middle option 'Don't display the alert and don't update automatic links'
Both together worked at me (may be one would have been enough?) with Windows 10.
Yiannis St commented
The "solution"to unlink the Onedrive folder from default C:\Users\xxxx\OneDrive and link it to C:\OneDrive to all computers, does the work of solving the problem with broken links inside excel files.
But the problem is when someone of the colleague uses an Apple PC. In Apple PCs there is no C:\ path. So does anyone knows who to solve this problem on an Apple MacBook f.e?
Rick of course commented
There doesn't seem to be a way of hyperlinking to specific cells (and presumably also words or sentences in Word) in Excel on Onedrive. There also doesn't seem to have the ability to have formulas linking and amending data between 2 or more Excels. IE. updating information on Excel 1 in certain cells, in certain ways, leads to a 1 of a variety of permutation results in Excel 2. This seems a severe oversight / issue.
I've not checked every possible action, but haven't found a solution to this, so assuming i'm correct, I'll get back to the slow, sarcastic clapping I save for other Microsoft "updates" and "improvements".
Microsoft is late to the game in cloud storage. Dropbox has a much better, more seamless integration to both Windows and Mac operating systems. To differentiate, OneDrive must offer something that Dropbox cannot. For us the ability to edit a Word, Excel, or PowerPoint file across time zones, locations and multiple users simultaneously is that feature. However, the update of file edits are still too slow, particularly when using the full featured desktop version of Office. The Cloud features of Word, Excel, PowerPoint are impressive given the cloud implementation, and updates are faster in this modality, but there are too many features lacking in the cloud / saas version to replace desktop functionality. Therefore in order of priority we need:
1. Significant speed improvements to match Dropbox on file updates and the synch of those updates to all users editing an Office file.
2. Better OS integration wtih files and folders similar to Dropbox (today we used only Dropbox for read-only viewing of files between teams, and selectively used OneDrive only when there is a need for team editing).
3. Over time, build better cloud / saas versions of Office to enable not only faster updates but to stay ahead of Google Docs. Today Google Docs are useful barebones tools but cannot nearly match Office features. That will change if Microsoft does not agressively upgrade cloud based functionality.
4. We'd like to see a roadmap of this feature set built into published material, not just for MSFTs largest enterprise clients but for small-medium businesses who in total probably amount to as many ore more users than those in the enterprise. We all watch what MSFT, Google and others do and daily vote with our dollars and usage of collaborative productivity tools. MSFT needs to be more transparent about timing relative to the competition.
Fred Dag commented
VBA code - Auto save Excel File to One Drive Folder keeps bugging out One Drive to Slow to to accept it
Plus that Auto Save set as Default OMG ! how ******** is that with no way of turning it off apart from on every file you open.
Just googled this issue and found it's been an unaddressed problem for three years. Great job Microsoft, you're really encouraging those o365 subscriptions.
ran into this problem. one way to possibly fix it is by creating links using =hyperlink(concatenate())
What i did was build my links using the concatenate function and hyperlinked them after. this way, excel is always calculating the link and not letting the link change.
hope that helps....
Ricky Tor commented
Unbelievable! a year past and NOTHING from Microsoft
the same problem. where is my microsoft?
Daniel Sanders commented
We have the same problem - hyperlinks in Excel 2016 saved to OneDrive sporadically break. Sometimes just one link. Sometimes several. I replace the links and a couple of days later some other links are broken.
Most of the hyperlinks are to Micsrosoft files stored on OneDrive.
The start of the link gets changed from "https://microsoft-my.sharepoint.com/" to "../../../:p:/g/personal/"
Peter Robinson commented
@Wiezman Kimchi. Make sure every PC that is going to access the files have OneDrive saved in a drive with the same letter. If one PC that has OneDrive saved in the user's profile or in a drive with a different letter, it immediately updates the links to that person's in order to find the linked cells. It does not undo it even if the person does not edit or save. Good luck!
Doug Scott commented
I no longer keep files on my laptop but save everything to OneDrive. I update Excel workbooks on OneDrive that have links to other workbooks and I no longer have a problem with changing links since I unchecked "Use office 2016 to sync Office files that I open" on the Office tab in the One Drive Settings.
Wiezman Kimchi commented
I have tried the solution that @PeterRobinson has suggested, and it didn't work. OneDrive is synced to a different folder on the computer (other than the default one).
Furthermore, when sending the file to my colleagues, they are requested to provide my credentials in order to open the file (they can dismiss the request for each of the files that is referenced, and only then get access to the file).
Is there a way to get this completely dismissed when sharing a file with other. In Excel there was an option to "update links" when you open the file, and this does not seems to popup when working with OneDrive.
Peter Robinson commented
I ran into this problem and found a simple solution. I have Excel spreadsheets in Dropbox linking to another in OneDrive. This is not a OneDrive problem but it’s just the way the links work. The path is created in the computer where it’s done. The problem is that both OneDrive and Dropbox by default create the offline folder under the user’s profile. So when you create a link, it will create the path within the user profile. I simple unlinked both OneDrive and Dropbox from my PC, and moved the folders from my profile, for example from C:\USERS/JOHNDOE/ONEDRIVE/DOCUMENTS/......., to C:\ONEDRIVE/..... Then I reconnected OneDrive, but this time after logging in, I select to change the location, and choose C:\OneDrive as the location. I did this to all my computers and they all open the folders and the links are working. The spreadsheet just sees the path and would not know it’s a different computer. It works for both OneDrive and Dropbox. Hope this helps.