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.