Understanding the Problem with Synced SharePoint or OneDrive Paths in Excel

The Issue

As a programmer and avid Excel user, I’ve encountered an intriguing problem. When using Excel to access files stored in a SharePoint library or a OneDrive network, which are synced locally via the Windows OneDrive app, the ThisWorkbook.path property behaves unexpectedly. Instead of returning the local path of the synced file, it reflects the SharePoint or OneDrive URL. This quirk has been a source of confusion and inconvenience in my workflow.

Technical Analysis

Technically, this issue arises because the OneDrive sync app creates a special kind of link between the cloud storage and the local filesystem. While files appear to be local, they maintain a strong association with their cloud counterparts. ThisWorkbook.path in Excel, being designed to show the actual source of the file, defaults to the cloud URL rather than the local path.

Code Overview

The provided VBA code addresses this problem. It’s a function named GetLocalPath, which converts a OneDrive URL to the corresponding local path. The function works by:

1. Checking the platform (Mac or Windows) and setting constants accordingly.
2. Validating the provided path to ensure it’s a OneDrive URL.
3. Utilizing various environment variables and directory structures specific to OneDrive’s sync settings.
4. Iterating through directories and files to map the URL to a local path.

This code is a modified version from a gist by Guido Witt-Dörring.

Usage:

Dim currentPath As String
currentPath = ThisWorkbook.path
If Left(currentPath, 5) = "https" Then
    currentPath = GetLocalPath(currentPath)
End If

Conclusion

As a developer, I find this code particularly ingenious. It elegantly circumvents a limitation in Excel’s handling of cloud-synced files, a testament to the power of creative coding. This solution not only enhances efficiency but also provides a deeper understanding of how cloud services integrate with local environments. It’s a perfect example of how a small piece of code can make a significant difference in everyday tasks.