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
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.
Office updated and still saving the activeworkbook.path as an "http://" address...
Renato Padua commented
Here in the company we have the same problem, we changed to sharepoint to escape the ransonwares and we got caught in this link problem between excel spreadsheets, it is clearly a bug because the first link works and the second already stops working
David Nailon commented
None of the solutions work, or will ever work. The files are being changed by the online onedrive.
It's not a bug, it's lazy programming and not understanding customer requirements.
I have tried to get around this for 2 days now and everytime i see a solution, it fails.
I like onedrive for collaboration, it's brilliant and no other product can do that. I can even understand why it does what it does and i actually agree that the functionality can be important. However it needs the ablility to be turned off and use local references when on a local repository and not automatically change them. As others have said, spreadsheets that work locally, fail when put into the local onedrive repository and that should NOT happen.
Hours of work is lost when onedrive just automatically changes your formatting. Honestly, what else are they changing ? Files should NOT be changed EVER without the authors PERMISSION. LINKS INCLUDED. PERIOD. **** like this makes me angry
It's NOT so hard to have 2 references .. an automatic online one and a local one. The problem is that developers work in an online remote world and right now they are focussed on office365.
for basic file sync and archiving !!! .. stick with google drive, dropbox. For collaboration with office products if you need them, Onedrive.
Carlos Lisboa commented
MS, PLEASE SOLVE THIS BUG URGENTLY!
THIS WORKED FOR ME!
Turn off automatic formatting of hyperlinks
If you don't want Excel to automatically format Internet and network paths as hyperlinks when you type, do the following:
Select the File tab and then select Options.
In the Excel Options dialog, select Proofing and then select AutoCorrect Options.
In the AutoCorrect dialog, select the AutoFormat As You Type tab.
Clear the check box for Internet and network paths with hyperlinks.
Select OK to close the AutoCorrect dialog.
Select OK to close the Excel Options dialog.
Anonymous, I perfectly agree with your statement. I have the problem for months now I switched form Dropbox to OneDrive and what I see now in this blog is that it's not supposed to change soon. Thanks for your contribution.
Blake Brett commented
I have this problem and it causes me lots of grief. A Excel spreadsheet in Sharepoint is used to link to other documents in Sharepoint, but occasionally when this file is updated all the links change and stop working for all staff.
This behavior is really disruptive. The worst is that EXCEL does the same when you link to local computer OUT of the OneDrive folder in the local C: root. and not only for files but also for FOLDERS. I understand that this is not quite correct, and can never work. I realized when I moved some working folders in which I move files around using macros out from OneDrive and relocated in a Local Library within "My Documents" trying to work this around. I got really surprised that instead of the hyperlink pointing to the local folder, it "invented" a location replacing the first part of the path by the SharePoint site string.
My conclusion is that this is NOT a "functionality" nor a "behavior", but either a simple and plane BUG. The fact that this is being dragged from 2015 suggests that this BUG is driven by an unacceptable and not very subtle SPAM practice to enforce people to work in the cloud OneDrive version of the documents and locations. It would be nice that we could work 100% cloud, and we users appreciate your efforts to put the means in place to get there but unfortunately, we aren't there yet. This also clashes the spirit of building the structures within companies from the base to the top in a user self-service way. I'm trying to automate some processes for my team and this behavior prevents me from doing it in a straight and simple way. It doesn't look professional and a program as Excel DO NOT deserve to hold these tricks. I think that you MS guys would do better fixing it for the time being.