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
@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.
This is a big problem as the blurb around one drive and office 365 indicated that this functionality was part of the package
I just lost the links to between two excel workbooks for the second time, thinking that the first time maybe I somehow didn't save the workbook--also thinking that the one drive file structure on my desktop computer was a local copy. Perhaps it isn't, because the links are gone from what I thought was my local copy as well as the 365 copy. Only the 365 worksheet copy was kind enough to tell me that links are removed. I've spent the afternoon moving my critical databases and excel worksheets to google drive.
Jim Feniello commented
In addition, the url to the reference shows up 3 different ways for me. Sometimes it uses a local reference (C:....), sometimes, a link to sharepoint.com but with the user name in the path, and sometimes the correct URL; path to the correct file location. Testing with sync'ing a sharepoint library with ODfB and with a sync'd O365 group library.