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
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.
@Micrsoft Experts, OneDrive Productmanagers and those who want to drive O365 consumption
I am wondering why Microsoft is not part of this discussion here.
Even if it is a bug or not we need to get some guidance whether you will fix this or what your recommendation regarding excel sheets within OneDrive/SharePoint is.
Microsoft wants decision maker in companies and It to drive consumption but on those points where is hard business impact e.g. in Controlling Departments you don’t care and let us alone.
I would really appreciate to get more insight on this issue and the according roadmap or the workaround by Microsoft.
By unchecking the OneDrive Office Sync checkbox you lose other features.
Thank you in advance for your feedback
Doug Scott commented
Solved Maybe- I've have had the same problem for last year with links to external workbooks, however today 03/18/18 I tried the solution of changing setting in One Drive and it seems to keep the links from changing. Right click on One Drive in Task Tray, click on Settings, click on Office Tab, uncheck "Use Office 2016 to sync Office files that I open". I've tested this several times and link remain. I also edited the Excel files using Office online and even though the links were disabled it still did not change them. If anything changes I'll add to this post.
PDL S - thank you so much. I has been a big issue for me - and now it is solved..... Great.....
Serneels Rudy commented
If you write a macro to save the file to a path, it automatic save the file to the one driven even if you do a SaveAs. Anybody has a solution for that ?
L Blank commented
We are syncing to Sharepoint online using files on demand.Our issue is that if you work on an XL file and have formulas linked to other XL files in the same sharepoint library the references get saved with the local path(ie C:\users\"loginName"\"SharepointSyncLocation"). This works when the author is working solely from one computer because once you try to edit on any other computer the links are no longer valid b/c the file does not exist in the same path as the author's path not to mention having any other users work with it. This renders working with linked spreadsheets on sharepoint online useless if you are trying to sync that library with OneDrive for business using Files on Demand. This is a serious flaw and needs to be addressed immediately
Victor S. commented
This issue gave me a lot of grief and I had to move my files to Google drive. The solution is as provided by some other people below:
- Right-click on the OneDrive blue icon in the taskbar,
- choose "Settings", then select the tab "Office"
- Uncheck the "Use Office 2016 to sync Office files that I open"
This also stops the pesky auto-save behavior of Office files when you're working on them, which in my case often causes sync issues.
Arihant Pamecha commented
The only way to keep links to local drive is by using "Save As" instead of "Save". However, if you "Save" the file which was previously "Saved As", the local drive link might get updated to an online address (company-mysharepoint...). Have written to MS and awaiting a solution to stop this from happening.
Daniel Watts commented
Not sure if mentioned here, but after much troubleshooting, the only way I could avoid this problem is by 'sharing' the OneDrive folder (C:\Users\User1\OneDrive...) with the user themselves.
Then mapping it via \\127.0.0.1 as a network drive and working exclusively through this. This seems to trick Excel into thinking it's not working through OneDrive and thus doesn't update to cloud links.
We're still experiencing this issue... Really just wanting to reference cloud files only, because we're using our master --> child --> grandchildren .xlsx/.xlsm files in a 100% collaboratively manner.