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
66 comments
-
Doug Scott
commented
I have work books that link to other workbooks and do not have this problem. just make sure you sync your OneDrive with your computer and open the workbooks on your computer.
-
Anonymous
commented
To Samm:
Thanks, your solution works but only if we do not update the links later... The problem remains and Microsoft is very disappointing -
Amby
commented
Hey Guys, I spent a few hours on this and realize lots of people have this problem. I got mine fixed. I am on Mac running o365.
The problem was that when I open the file, excel complains a broken link points to a OneDrive path that seems to be invalid as it looks like my local path. Not sure how I got to that. All I can think of is may be because I have been working on local files and Save As to OneDrive to backup my working versions.
So, back to the solution, I first tried to do a Edit->Find on link name on all formulas, content, etc.. Didn't find anything.
I then create a new blank workbook, from the original workbook, I select all sheets from sheets tab, do a Move / Copy..., and select the blank work book. All sheets copied over.
I then save and reopen the new workbook. Found out the problems stick. I then went ahead and delete one sheet at a time. Each time, I save, close, and re-open to see when the problem goes away.
I finally got to one sheet that caused the problem.
After spending some time, I find out the problem is related to Data Validation. I have cells that use Data Validation to create a drop down list. I guess it must have pointed to remote file. Unfortunately Data Validation does not get searched as formulas.
I clear the cells, problem went away. I recreate the data validation again. Happy Day for me!
Lesson learn, I think if a link is created in features that's not in a formula, find the broken link is impossible. One can only narrow down to which sheet and then check Data Validation, Conditional Formatting, or any feature that can link to other sheets.
Hope that helps.
Amby.
PS. Man, it's been 10 years since last time I used a spreadsheet. Gotta say, o365 makes me feel like it has only advanced 1 years compares to other solution out there that are growing a light speed. I think that's what happens when too much comfortable passive revenue from the existing user base makes business lazy to continuously improve.
-
Doug Scott
commented
I no longer have this problem since I started syncing One Drive to my local hard drive.
-
Anonymous
commented
This is a really annoying issue. Excel keeps on referencing an external URL but the rest of the URL is a location on my local drive. No way of removing it so I have to manually update every time. Please fix or I will go back to Box.com
-
Nick
commented
This is still a big problem on O365 Mac Excel. When you re-open a file with links to a local source file, the links are automatically changed to a long hyperlinked file address. Excel says it can't find the file, then I have to go re-point the Data Source from Remote File to Local File. Very annoying and no options to fix on a mac.
-
Samm
commented
Go to File > Options > Advanced > At general section select Web Options > Select Files tab and untick "Update links on save'.
However the formula that file path had already change, will have to change back manually.. if only i found out this before my formula changed to file path..
-
Steve
commented
Hello,
i tried two things: write 'file:///' before the address in the 'Insert Hyperlink'-popup. And another thing helped: Menu Data -> Edit Links -> Button 'Startup Prompt...' -> choose the middle option 'Don't display the alert and don't update automatic links'
Both together worked at me (may be one would have been enough?) with Windows 10. -
Yiannis St
commented
The "solution"to unlink the Onedrive folder from default C:\Users\xxxx\OneDrive and link it to C:\OneDrive to all computers, does the work of solving the problem with broken links inside excel files.
But the problem is when someone of the colleague uses an Apple PC. In Apple PCs there is no C:\ path. So does anyone knows who to solve this problem on an Apple MacBook f.e?
-
Rick of course
commented
There doesn't seem to be a way of hyperlinking to specific cells (and presumably also words or sentences in Word) in Excel on Onedrive. There also doesn't seem to have the ability to have formulas linking and amending data between 2 or more Excels. IE. updating information on Excel 1 in certain cells, in certain ways, leads to a 1 of a variety of permutation results in Excel 2. This seems a severe oversight / issue.
I've not checked every possible action, but haven't found a solution to this, so assuming i'm correct, I'll get back to the slow, sarcastic clapping I save for other Microsoft "updates" and "improvements".
-
Anonymous
commented
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
Usless
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.
Microsoft Suck -
JD
commented
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.
-
Anonymous
commented
hey guys
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
-
Anonymous
commented
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.
