SOLVED

Edit links - change source file - error message ' Cannot access XXX.xlsx

Copper Contributor

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

8 Replies

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?!

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.

 

Any ideas?

 

Alberto.

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"

 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_drive-mso_win10-mso_2016/excel-links-res...

 

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.

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.

Thank you.

@Joanna Hill 

best response confirmed by Alan Umanos (Microsoft)
Solution

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.

Regardless of external links being best practice or not, this cannot be expected behaviour. I frequently use "Edit links" to remove external links in files, and link elements to my current file instead. Scenario:

I am copying a fairly advanced graph from previous months report to current months report. The report file is system-generated and so is not reused. I also copy the graph's source data sheet to my current report file. Now the graph is still fetching data from prev. mths file. This I normally fix by "Edit links" and pointing to my new current report file to remove that link. Now with Sharepoint I get the error "Cannot access...". As a work-around I have to temporarily save my report file to C:/Temp, open file from this location, update links to point to the file in C:/Temp to remove the links, and save. Then I copy the file to sharepoint again and open to continue working with the file. 

I'm sure that might work for you personally, but what about your coworkers? If the data source is pointing to C:\Temp on your machine, I can't imagine other users would be able to successfully refresh the data link.

For your situation, I would assess if there is a need for that external links in the first place if the system generated report isn't reused.

@Alan Umanos Hi, you might have read my post a bit swiftly; The C:/Temp store is just a temporarily location so I can run Edit links. While the file is stored temporarily in C:/Temp I can change the links to point to the file itself. This way the graph will fetch data from the datasheet in my current file instead of in the previous months report file. 

1 best response

Accepted Solutions
best response confirmed by Alan Umanos (Microsoft)
Solution

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.

View solution in original post