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
Anonymous
shared this idea
92 comments
-
Caleb
commented
hey guys, I've managed to make the reference link to stick to the local filepath instead or reverting to the remote URL link everytime the file is reopen.
Solution:
- Step1: Break all the links in the excel file (make sure u keep the formulas cause u'll need it again to rebuilt the connection)-Step2: Cut the excel file and paste it in either another folder or a subfolder (goal: make sure both excel file isn't in the same folder)
-Step3: Rebuild your formulas
I know this sounds weird, but simply moving the excel file to another folder kinda works for me, making the reference link stick to the local filepath instead the URL webpath.
-
Howard
commented
This impacts our staff's productivity a lot.
-
Anonymous
commented
This is especially horrible with Excel not being able to handle long paths. Sharepoint URLs are very long and if your system forces them even for files that are side by side in the same directory, everything will break if your path is reasonably long.
Excel actually will silently break links between files if the paths are too long.
-
Anonymous
commented
In excel the cell("Filename") function returns URL rather than absolute file path. The URL points to my synced files on OneDrive and not the file on my PC. Any ideas, anyone?
-
Anonymous
commented
Every time this 'feature' is implemented, it breaks my worksheet. Why can you not just leave this alone unless I opt into the 'feature'.
or, could you be nice and allow me to turn it off?
I now have to create a macro to undo your feature every time I open the workbook.
Not Happy Jan........
-
Bill
commented
The "Use Office applications to sync Office files that I open" solution works, but by definition it breaks all the new, wonderful file collaboration stuff MS is doing. This issue needs its own solution, Microsoft!
-
Anonymous
commented
I have tried to option 'Use Office application to sync Office files that I open", but then the files are only local. The link goes to C:\Users\<username>\OneDrive.
But we want to the link to be https://<sharepointcompany>.sharepoint.com/sites/.....
This is working when files are in the same folder or subfolder, but not if they are in different folders.
That is weird!? -
ArnfinnR
commented
I believe this problem was solved by the solution published by Anonymous August 03, 2016 22:15!
At least that solution solved the problem for me:1: Right-click on the OneDrive cloud icon (in the task bar lower the right corner)
2: choose Settings
3: choose Office (Tab)
4: uncheck the "Use Office applications to sync Office files that I open"That's it!
After this, all links inside Excel, Word, etc. to file paths on local drive (C: etc.) are kept instead of constantly being replaced with https://d.docs... etc. -
Germain
commented
It looks like this problem has been going on for years... I thought I was the only guy that had the problem. Has anyone from Microsoft had any comments about this? Any solutions? It would be nice to hear from them.
Thanks,
GA -
CVAR
commented
I just lost over 400 hours of work due to this. We lost ALL of our contract maintenance and summary data for an $8m account.
Without a solution to this soon we will cancel all microsoft usage and seek other solutions.
-
Usman
commented
We are facing a lot of issue with this behavior. Its affecting our work as we need to work with different files from different folders.
-
Robbe Moerman
commented
I use an excel that refers to txt-files. I need them to open in Notepad, but as Excel constantly changes the hyperlinks, they always open in a browser. I wrote a macro that overwrites these hyperlinks on open, but I see every time that Excel changed them first and got overwritten. This is incredibly annoying.
-
James Blakey-Milner
commented
Please God fix this.
This issue just resulted in several projects worth of files being corrupted. They will have to be manually re-linked and will cost my company days of effort. We are all professionals, the cost of lost time is easily an order of magnitude over the yearly 365 subscription.
Will consider alternatives.
-
Anonymous
commented
And yet, it works perfectly well with Dropbox. The irony.
-
j
commented
I fixed mine. I had issues with sumif( ) type functions looking in other files.
It takes a while but you only have to do this once.
Make sure all the connected files are in OneDrive.
Use the GetData wizard to import the data you are linking to for your calculations into the same spreadsheet as the formula. default behaviour is to dump the data into a new sheet as a Table. Rename the Table and the worksheet. add some text explaining where this random data comes from. because good practice.
Go to the formula that used to reference the data directly in the other worksheet (which now doesn't work) and point it to the new Table.So long as the other workbooks aren't open, to get fresh data, just hit Data/RefreshAll.
-
Doug Scott
commented
I can confirm that if the workbooks are in the same folder the links will update fine but if they are in different folders they will not and you get a warning when you open the workbook that the links will not update.
-
Jason Brad
commented
It has been four years past, NOTHING changed, what happend to Microsoft?
-
Aaron Moayed commented
SOLUTION (Doug Scott found it):
"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."I tried the rest of the offered solutions here, and his was the only one that worked!
-
Bartłomiej Michna
commented
This also prevents the auto-refreshing of external data references in Powerpoint. The application doesn't even mention that will be a case, but it's happening in Office 365 now.
-
Brad Wilson
commented
I use Excel a lot for my business, which at times involves working on some complex business modelling and a lot of collaboration with external parties.
When I receive a spreadsheet that has had several hundred hours of work spent on it by some highly paid people, and simply by opening it I cause all of the external references to get replaced by an online URL to my specific onedrive account for no good reason and without me asking it to do so, those highly paid people and their bosses and their clients get really mad.
This brain-**** of a feature has already resulted in god-knows how many thousands of dollars of wastage because I was the guy who opened it from Onedrive (more than once, I'm embarrased to admit), so whatever collaborative benefits it was designed to deliver is, in my case at least, utterly negated by the impacts of some very common real-world scenarios that must occur in offices every day.
The only way around this is to disable the Onedrive option "Use Office applications to sync Office files that I open", and be sure to only ever open Excel files locally. This is unfortunate, because this option also applies to Word and Powerpoint files too, and ultimately means that the otherwise great collaboration features of Office go unused; all because of one silly weakness in just one part of the Office suite.
Microsoft, you do some good stuff these days, but it's nuanced issues like this that have really big impacts and weaken the rest of your products (there are many other issues of a similar nature) that I just shake my head at in disbelief - more so when it has been a known and ongoing issue for a number of years.
