Forum Discussion
Edit links - change source file - error message ' Cannot access XXX.xlsx
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.
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.
