01-30-2018 07:59 AM
01-30-2018 07:59 AM
I have a destination workbook which contains links to various source documents. I have used this set up for months and have never had a problem. Each month I click edit links - change source - and then click on the new source file that I want to use for the current month. This month when I have done that I keep getting an error message 'Cannot access XXXX.xlsx'. Help!!
I always try to link to the file on my local PC, but have noticed that the links always change to d.docs.live.net/[strange string of letters/numbers] - I assumed this was just something to do with working with OneDrive but don't know if this is causing a problem?
Thanks in advance
01-30-2018 08:03 AM
Strangely enough it let's me link to the file I want if I save that file to my desktop and then select it from there?!
05-29-2018 10:34 AM - edited 05-29-2018 10:34 AM
Same problem here, but only when the new soruce file is already open. If it's closed link is changed, although it takes ages to update values in target excel! BTW, only happens if source is in Sharepoint synced folder. If it's in My Desktop no error.
09-14-2018 03:48 PM
This is just a massive problem. My company moved to sharepoint (which is terrible in almost every way) and any file linked to another file on sharepoint doesn't work when you save, close, and reopen.
Apparently a fix is to turn off "sync"
So you either have to choose between having linked files saved on sharepoint work or being able to make changes and not "save as" with your coworkers.
Office 365 is essentially in alpha or beta mode right now when you use Sharepoint as your file storage. Nothing works as it should and there is no way a F500 company should make the switch. At one point, Microsoft accidentally released an update making Control-H not work anymore so their review process is very poor. Save times are abysmal on sharepoint as well.
06-12-2019 03:04 AM
I am used to "insert Links" in order to link to OneDrive files. Now, when I try to insert a Link, I get a VERY limited pop-up box that does not allow me to Browse any more to find my files. Even when I go to OneDrive, copy the link, then paste it in (so much harder), the link doesn't work.
I am not tech-savvy like many threads show, yet I used to store tons of links to OneDrive (how perfect !!!) Now, I cannot add links, nor can I access links I have made (that worked) over time !!! Something broke or got limited, I have a call in for help but so far, no one has come up with a fix to something that used to work so nicely.
06-20-2019 06:22 PM - edited 06-20-2019 06:48 PMSolution
I'm 100% sure this is expected behavior.
If I create two new Excel Workbooks in my OneDrive sync folder (named source.xlsx and client.xlsx), and create a link in client.xlsx for data hosted in source.xlsx, the link shows the URL of source.xlsx as it exists in OneDrive, not the local OneDrive folder's UNC path.
If I have both worksheets open and try to change the source (Data > Queries & Connections > Edit Links > Change source) in order to change the path for Source.xlsx from the OneDrive site URL to the OneDrive local sync folder UNC path on my local machine, I will see the "Cannot access 'source.xlsx'." message.
If I close source.xlsx and try again, I do not get the pop up and the location is updated successfully (however it will revert back to the URL, which is expected, but I'll come back to that point in a moment).
When I open an Office file from my local OneDrive Sync folder, the green check mark from OneDrive indicating the file is available changes to the blue "Sync pending" icon. The pop up doesn't happen when the sync isn't running or if the source file is closed. OneDrive has the files in an active sync state when they're opened, which locks them, hence the resulting popup. When the source file is no longer locked/in use, I can update my client.xlsx file's link source to source.xlsx with no problem.
Seeing the URL instead of the UNC path (C:\...\OneDrive\SyncedFilePath...) is expected for how OneDrive works. When you open an Office file from the local OneDrive folder while OneDrive sync is running and connected to a network, you're opening the file from your OneDrive site in the cloud, not from your local machine. This is to ensure you're opening the most current version of the file.
Normally I'd expect saving the file to fail to since OneDrive has the file locked for the sync, however it's important to remember that when you open a file from OneDrive with an internet connection and the sync running, you're not opening the file from your hard drive, you're opening it from it's cloud location. Any edits made are saved to the cloud, not the file in the local OneDrive sync folder on your Hard drive. OneDrive is downloading the file from the cloud, while any file changes are uploaded to the cloud by Office. You have two of the same file in two different locations, not one file.
Excel worksheet external links aren't great practice to begin with. Excel is not a database and shouldn't be used like one. Better practice might be to link Excel to a database or Power Bi instead of linking Excel to Excel. Heck, using a SharePoint list can be better than Excel depending on the purpose and need.