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
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.
Same problem, any fix yet?
For some reason if you do a Save As each time rather than Save it will not break or change the link. Not a very good solution but it works.
Same issue and none of the suggested fixes in this thread work, that is my version of Excel (Office 365 Business Premium) even mesh with the suggested fixes.
Toomas Runnel commented
I had the same problem in 2013 already and I used 2 different fixes for this matter:
- Work laptop I rolled back to office 2010
- Home desktop i moved lot of stuff to dropbox, so still having the cloud benegfits without it's hassles
- Later i got a dropbox 25 gb boost, so i could move my work stuff over too.
Hope MS makes this fix more obvious and more easy to find for the less adept users, otherwise people need to switch to other services...
We had to stop using OneDrive because it changes all of the external links. I'm keeping everything on my laptop for editing and then using a third party backup program to sync my files to OneDrive for viewing only on phone and tablet. This link changing is a real Microsoft ***** up.
How can we stop cursor after pressing enter on one drive excel sheet on mobile
Normally this option found in PC excel file under Advance setting. but same advance option is not found in one drive online sheet.
Please help on this if any.........
The only way I've found to keep this from happening is to do a Save As on the workbook containing the link instead of doing a Save. This way the link will continue to point to the local OneDrive instead of the OneDrive web location. However if you forget and do a Save all of the external links will be toast again.
None of the suggested fixes have worked for me... This issue is still ongoing :(
I found the "Settings" from the OneDrive cloud icon and removed the check mark to the "Use Office 2016 to sync Office files that I open"
Still DOES NOT WORK.
AM I the only person left int he world with this problem???
everyone stopped posting in dec 2016