The OneDrive Nightmare Continues: ThisWorkbook.Path and ThisWorkbook.FullName

I posted here about my OneDrive backup everything nightmare. Another bad dream has reared it's ugliness in VBA land: ThisWorkbook.Path and ThisWorkbook.FullName.

For some reason I absolutely cannot fathom, Excel returns a useless URL when reading these two properties in a workbook opened from any OneDrive or Sharepoint shared location. For example, ThisWorkbook.FullName value when opened from any location other than a OneDrive shared folder:

C:\Users\Public\Just Another Workbook.xlsx

But, put that workbook in any OneDrive shared folder, and this is returned:

https://datapros-my.sharepoint.com/personal/kjones\_dataautopros\_com/Documents/Just Another Workbook.xlsx

where the actual local path is:

C:\Users\Kevin Jones\OneDrive - Data Automation Professionals\Just Another Workbook.xlsx

Right off the bat it's painfully obvious that the URL is absolutely useless to anyone and everyone. One of the most common automation tasks that use ThisWorkbook.FullName or ThisWorkbook.Path is to navigate around inside the folder in which the workbook is located. There is no easy way to translate that URL into a local path. It is possible but it's a tricky operation - more on that later. What else is that URL good for? I have no idea.

The whole idea of sharing folders and files on a local drive is so that they can be used the same way as if they were in a non-shared folder. So we don't have to go to the online location and either download the file or open it in a browser. DropBox did it right. Google Drive did it right. So what were the Microsoft engineers thinking by exposing the URL versus the local path? Anyone have a clue?

A lot of people have said to turn off this OneDrive option. Even if that did work, it is no longer available in the current OneDrive UI/UX.

People have written URL parsers and file search routines to try to translate the URL into a local path. This StackOverflow thread got pretty crazy with ideas but none of the solutions worked in all scenarios. Remember that OneDrive supports one personal account, up to nine business accounts, and Sharepoint folders - each of these have different structures in their URLs without any obvious mapping to the folder on the local drive. I did find one possible solution in the StackOverflow thread that showed promise - I reworked it so that it worked in as many scenarios as I could find. I'm posting it below.

But, before we get to that, a message to Microsoft: Please refrain from serving up URLs in ThisWorkbook.FullName and ThisWorkbook.Path when the workbook is stored or opened on a local drive. It serves no purpose other than to annoy us and make our lives more difficult. It's also not reality as the workbook is, in fact, located on the local drive in a local folder - that's from where Excel opens it and saves it. Keep the URL an internal thing (I realize it's needed for real time collaboration) and, if you actually believe that someone out here can use it, add a new ThisWorkbook property such as SharedURL.

Below is the routine for getting the local path as it should be returned in FullName. It's been tested on multiple machines, Windows 10 and 11, and Sharepoint and OneDrive shared folders. Post to this thread if you make any improvements or fix any issues.

Public Function OneDriveLocalFilePath( _

        Optional ByRef OneDriveFilePath As String _

    ) As String

' Returns the local file path given a URL to a file stored in a OneDrive or Sharepoint folder. For

' some reason the Excel Workbook properties Path and FullName return URLs instead of local paths.

'

' OneDriveFilePath - Any valid local path or URL referencing a OneDrive file. If the path cannot be

'   resolved, the original path is returned.

    Dim WScript As Object

    Dim WinMgmtS As Object

    Dim Result As String

    Dim ProposedFilePath As String

    Dim ConfirmedFilePath As String

    Dim RegistryKey As Variant

    Dim RegistryKeys As Variant

    Dim Types As Variant

    Dim CID As String

    Dim MountPoint As String

    Dim URLNamespace As String

    Dim Path1 As String

    Dim Path2 As String

    Dim Directories As Variant

    Dim ParentDirectory As String

    ' Default to the full name property of ThisWorkbook

    If Len(OneDriveFilePath) = 0 Then

        OneDriveFilePath = ThisWorkbook.FullName

    End If

    ' Deterimine if the path is a URL or a local path

    If Left(OneDriveFilePath, 8) = "https://" Then

        ' WScript and Winmgmts are used to navigate the registry

        Set WScript = CreateObject("WScript.Shell")

        Set WinMgmtS = GetObject("Winmgmts:root\default:StdRegProv")

        ' Enumerate the key HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive

        If WinMgmtS.EnumKey(&H80000001, "SOFTWARE\SyncEngines\Providers\OneDrive", RegistryKeys, Types) = 0 Then

            For Each RegistryKey In RegistryKeys

                ' Each key has three interesting values:

                '

                '   CID - Some hash code sometimes used in the path

                '   URLNameSpace - The URL to a parent directory in the cloud

                '   MountPoint - The local path OneDrive uses to mirror files found in the URLNameSpace address

                CID = vbNullString

                MountPoint = vbNullString

                URLNamespace = vbNullString

                ProposedFilePath = vbNullString

                ConfirmedFilePath = vbNullString

                On Error Resume Next

                CID = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\CID")

                MountPoint = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\MountPoint")

                URLNamespace = WScript.RegRead("HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive" & RegistryKey & "\URLNamespace")

                On Error GoTo 0

                ' It's not always clear how for down the folder tree the URL and the mount point go so the file's parent is

            ' pulled from the OneDrivePath so that it can be compared with and without

                Directories = Split(OneDriveFilePath, "/")

                ParentDirectory = Directories(UBound(Directories) - 1)

                ' Remove any trailing slash from the URL name space

                If Right(URLNamespace, 1) = "/" Then

                    URLNamespace = Left(URLNamespace, Len(URLNamespace) - 1)

                End If

                ' Build two paths to test against: one without the CID and one with the CID

                Path1 = URLNamespace & "/"

                Path2 = URLNamespace & "/" & CID & "/"

                ' Try the path without the CID

                If Left(OneDriveFilePath, Len(Path1)) = Path1 Then

                    ' Try building the final local path from the mount point path and the unmatched end of the OneDrive path

                ' and return it if the file exists

                    ProposedFilePath = MountPoint & "" & Replace(Replace(Mid(OneDriveFilePath, Len(Path1) + 1), "/", ""), "%20", Space(1))

                    If ExistingFile(ProposedFilePath) Then

                        ConfirmedFilePath = ProposedFilePath

                        Exit For

                    End If

                    ' Try building the final local path from the mount point path and the unmatched end of the OneDrive path

                ' but without the first folder and return it if the file exists

                    If Right(MountPoint, Len(ParentDirectory)) = ParentDirectory Then

                        ProposedFilePath = Replace(Replace(Mid(OneDriveFilePath, Len(Path1) + 1), "/", ""), "%20", Space(1))

                        ProposedFilePath = Mid(ProposedFilePath, InStr(ProposedFilePath, "") + 1)

                        ProposedFilePath = MountPoint & "" & ProposedFilePath

                        If ExistingFile(ProposedFilePath) Then

                            ConfirmedFilePath = ProposedFilePath

                            Exit For

                        End If

                    End If

                End If

                ' Try building the final local path from the mount point path with the CID attached and the unmatched end

            ' of the OneDrive path and return it if the file exists

                If Left(OneDriveFilePath, Len(Path2)) = Path2 Then

                    ProposedFilePath = Replace(Replace(Mid(OneDriveFilePath, Len(Path2)), "/", ""), "%20", Space(1))

                    ProposedFilePath = Mid(ProposedFilePath, InStr(ProposedFilePath, "") + 1)

                    ProposedFilePath = MountPoint & "" & ProposedFilePath

                    If ExistingFile(ProposedFilePath) Then

                        ConfirmedFilePath = ProposedFilePath

                        Exit For

                    End If

                End If

            Next RegistryKey

        End If

        ' Return the confirmed file path if a valid path was found

        If Len(ConfirmedFilePath) > 0 Then

            Result = ConfirmedFilePath

        Else

            Result = OneDriveFilePath

        End If

    Else

        ' The path is not a URL so return it as-is

        Result = OneDriveFilePath

    End If

    OneDriveLocalFilePath = Result

End Function

Public Function ExistingFile( _

        ByVal FilePath As String _

    ) As Boolean

' Returns True if the file exists, False otherwise. This routine does not use the Dir technique as

' the Dir function resets any current Dir process.

'

' FilePath - Full path to the folder or file to be evaluated.

    Dim Attributes As Long

    On Error Resume Next

    Attributes = GetAttr(FilePath)

    ExistingFile = (Err.Number = 0) And (Attributes And vbDirectory) = 0

    Err.Clear

End Function

Kevin