Handling the Obfuscation of ThisWorkbook.Path and .FullName for OneDrive and Sharepoint Files

VBA

A workaround to convert the obfuscated OneDrive and Sharepoint URLs returned by these two properties into valid local file paths.

As we have continued to experience OneDrive, the frustrations with it continue. This article is about a specific challenge in the Excel VBE: the two properties ThisWorkbook.Path and ThisWorkbook.FullName.

For some reason we 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, consider this ThisWorkbook.FullName value when the workbook is opened from any location other than a OneDrive shared folder:

C:\Users\User Name\Just Another Workbook.xlsx

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\User Name\OneDrive - Data Automation Professionals\Just Another Workbook.xlsx

Right off the bat it's painfully obvious that the URL is useless to anyone and everyone. One of the most common automation tasks that use ThisWorkbook.FullName or ThisWorkbook.Path is to navigate around using the folder in which the workbook is located as a starting point. 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? We have no idea.

The whole idea of sharing folders and files on a local drive is so they can be used the same way as if they were in a non-shared folder, and 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. What were the Microsoft engineers thinking by exposing the URL to us versus the local path?

A lot of people have said to turn off some 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. A StackOverflow thread got 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. We did find one possible solution in that thread using information stored in the Registry that showed promise. Starting with that, we reworked it so that it worked in as many scenarios as we could find.

The Fix

Below is the routine for getting the local path as it should be returned in FullName. It has been tested on multiple machines, Windows 10 and 11, and SharePoint and OneDrive shared folders. The helper routines DecodeURL, ExistingFile, and ExistingFolder are included, so the code can be pasted into a module as-is. Let us know if you make any improvements or fix any issues.

Public Function OneDriveLocalFilePath( _
        Optional ByVal OneDriveFilePath As String, _
        Optional ByVal ReturnFolderPathOnly As Boolean, _
        Optional ByVal ReturnEmptyIfFileNotFound As Boolean, _
        Optional ByRef ErrorMessage 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 or folder. If the path
'   cannot be resolved, the original path is returned. Optional. If omitted then
'   ThisWorkbook.FullName is assumed.
'
' ReturnFolderPathOnly - Pass True to return the folder path only. This is the equivalent of
'   ThisWorkbook.Path. Optional. If omitted then False is assumed.
'
' ReturnEmptyIfFileNotFound - Pass True to return an empty string if the file cannot be found, False
'   to return the original path. Optional. If omitted then False is assumed.
'
' ErrorMessage - Receives the error message if an error was encountered. Optional.
'
' Notes:
'
' Debugging information is written to a text file in the Desktop folder when the conditional
' compilation argument Debugging is set to -1. This argument can be set in this module or in the
' project properties dialog.
'
' When more than one existing file matches and the second pass cannot narrow it down to one, the
' first file found is returned.

    Const RegistryPath As String = "HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive\"
    
    Dim Shell As Object
    Dim WinMgmtS As Object
    Dim Result 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 WebURL As String
    Dim URLNamespace As String
    Dim FullRemotePath As String
    Dim URLInstance As Long
    Dim URL As String
    Dim URLCount As Long
    Dim LibraryType As String
    Dim URLExtended As String
    Dim LocalPartialPath As String
    Dim PartialPathRootDirectory As String
    Dim RemovedRootDirectory As String
    Dim Pass As Long
    Dim EntryCount As Long
    Dim ExistsCount As Long
    Dim Log As String
    Dim LogFilePath As String
    Dim FileNumber As Long
    
    OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Entering OneDriveLocalFilePath"
    
    ' Default to the full name property of ThisWorkbook
    If Len(OneDriveFilePath) = 0 Then
        OneDriveFilePath = ThisWorkbook.FullName
    End If
    
    ErrorMessage = vbNullString
    
    OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "OneDrive file path: " & OneDriveFilePath
    
    ' Determine if the path is a URL or a local path
    If Left(OneDriveFilePath, 8) = "https://" Then
        
        ' The WScript shell and the WMI registry provider are used to navigate the registry
        Set Shell = CreateObject("WScript.Shell")
        Set WinMgmtS = GetObject("Winmgmts:root\default:StdRegProv")
        
        For Pass = 1 To 2
            
            ExistsCount = 0
            ConfirmedFilePath = vbNullString
            
            OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Pass " & Pass & " - evaluating registry entries in '" & RegistryPath & "'"
            
            ' Enumerate the key HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive - the key list is not an array if the key is missing or has no subkeys
            RegistryKeys = Empty
            WinMgmtS.EnumKey &H80000001, Mid(RegistryPath, InStr(RegistryPath, "\") + 1), RegistryKeys, Types
            If IsArray(RegistryKeys) Then
                
                EntryCount = 0
                
                For Each RegistryKey In RegistryKeys
                    
                    OneDriveLocalFilePath_WriteDebuggingLog Log
                    
                    ' Each key has five values of interest:
                    '
                    '   CID - Some hash code sometimes used in the path
                    '   WebURL - The URL to a parent directory in the cloud - it seems to be a parent directory of the name space URL
                    '   URLNameSpace - Another URL to a parent directory in the cloud - this is the main "name space" URL
                    '   FullRemotePath - A third URL to a parent directory in the cloud - it seems to be a subordinate directory of the name space URL
                    '   MountPoint - The local path OneDrive uses to mirror files found in the URLNameSpace address
                    
                    CID = vbNullString
                    MountPoint = vbNullString
                    WebURL = vbNullString
                    URLNamespace = vbNullString
                    FullRemotePath = vbNullString
                    LibraryType = vbNullString
                    
                    On Error Resume Next
                    CID = Shell.RegRead(RegistryPath & RegistryKey & "\CID")
                    MountPoint = Shell.RegRead(RegistryPath & RegistryKey & "\MountPoint")
                    WebURL = Shell.RegRead(RegistryPath & RegistryKey & "\WebURL")
                    URLNamespace = Shell.RegRead(RegistryPath & RegistryKey & "\URLNamespace")
                    FullRemotePath = Shell.RegRead(RegistryPath & RegistryKey & "\FullRemotePath")
                    LibraryType = Shell.RegRead(RegistryPath & RegistryKey & "\LibraryType")
                    On Error GoTo 0
                    
                    EntryCount = EntryCount + 1
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Evaluating registry entry " & EntryCount
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "Key:", (RegistryKey)
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "CID:", CID
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "WebURL:", WebURL
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "URLNamespace:", URLNamespace
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "FullRemotePath:", FullRemotePath
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "MountPoint:", MountPoint
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "LibraryType:", LibraryType
                    
                    If Len(MountPoint) > 0 Then
                        
                        URLCount = 0
                        
                        For URLInstance = 1 To 3
                            
                            Select Case URLInstance
                                Case 1
                                    URL = URLNamespace
                                Case 2
                                    URL = WebURL
                                Case 3
                                    URL = FullRemotePath
                            End Select
                            
                            If Len(URL) > 0 Then
                                
                                URLCount = URLCount + 1
                                
                                OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "Evaluating URL:", URL
                                
                                ' Remove any trailing slash from URL
                                If Right(URL, 1) = "/" Then
                                    URL = Left(URL, Len(URL) - 1)
                                End If
                                
                                ' Determine extended URL name space which may or may not include CID
                                If Len(CID) = 0 Then
                                    URLExtended = URL
                                Else
                                    URLExtended = URL & "/" & CID
                                    If StrComp(Left(OneDriveFilePath, Len(URLExtended)), URLExtended, vbTextCompare) <> 0 Then
                                        ' Personal OneDrive URLs always include the account CID so an entry for a different account is skipped - business URLs never include the CID
                                        If StrComp(Left(URL, 23), "https://d.docs.live.net", vbTextCompare) = 0 And StrComp(Left(OneDriveFilePath, Len(URL)), URL, vbTextCompare) = 0 Then
                                            URLExtended = vbNullString
                                        Else
                                            URLExtended = URL
                                        End If
                                    End If
                                End If
                                
                                ' Looking for a local file path only if the base of the path being evaluated matches the extended URL
                                If Len(URLExtended) = 0 Then
                                    
                                    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Account CID does not match the CID in the OneDrive file path."
                                    
                                ElseIf StrComp(Left(OneDriveFilePath, Len(URLExtended)), URLExtended, vbTextCompare) = 0 Then
                                    
                                    LocalPartialPath = Mid(OneDriveFilePath, Len(URLExtended) + 2)
                                    LocalPartialPath = DecodeURL(Replace(LocalPartialPath, "/", "\"))
                                    
                                    ' Personal OneDrive URLs replace some characters with caret escape codes
                                    If StrComp(Left(URLExtended, 23), "https://d.docs.live.net", vbTextCompare) = 0 Then
                                        LocalPartialPath = OneDriveLocalFilePath_DecodeCaretEscapes(LocalPartialPath)
                                    End If
                                    
                                    RemovedRootDirectory = vbNullString
                                    
                                    Do While Len(LocalPartialPath) > 0
                                        
                                        ' It's not clear how much of the local partial path is used for the local path so all possible paths are tried by removing the highest level folder each pass until there are none left
                                        If InStr(LocalPartialPath, "\") > 0 Then
                                            PartialPathRootDirectory = Left(LocalPartialPath, InStr(LocalPartialPath, "\") - 1)
                                        Else
                                            PartialPathRootDirectory = vbNullString
                                        End If
                                        
                                        ' The second pass only considers team sites where the mount point ends with the directory most recently removed from the partial path, meaning the remaining partial path is located under the mount point
                                        If Pass = 1 Or (Pass = 2 And StrComp(LibraryType, "teamsite", vbTextCompare) = 0 And Len(RemovedRootDirectory) > 0 And StrComp(Right(MountPoint, Len(RemovedRootDirectory)), RemovedRootDirectory, vbTextCompare) = 0) Then
                                            OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Local partial path:", LocalPartialPath
                                            If OneDriveLocalFilePath_FileExists(MountPoint & "\" & LocalPartialPath, ConfirmedFilePath, ExistsCount, Log) Then Exit For
                                        Else
                                            If Pass = 2 Then
                                                ' Log the partial path that was skipped and the reason
                                                If StrComp(LibraryType, "teamsite", vbTextCompare) <> 0 Then
                                                    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Skipped partial path:", LocalPartialPath & " (not a team site)"
                                                ElseIf Len(RemovedRootDirectory) = 0 Then
                                                    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Skipped partial path:", LocalPartialPath & " (no directory removed yet)"
                                                Else
                                                    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Skipped partial path:", LocalPartialPath & " (mount point does not end with '" & RemovedRootDirectory & "')"
                                                End If
                                            End If
                                        End If
                                        
                                        ' Remove the root directory and try again
                                        If Len(PartialPathRootDirectory) > 0 Then
                                            RemovedRootDirectory = PartialPathRootDirectory
                                            LocalPartialPath = Mid(LocalPartialPath, InStr(LocalPartialPath, "\") + 1)
                                        Else
                                            LocalPartialPath = vbNullString
                                        End If
                                        
                                    Loop
                                    
                                Else
                                    
                                    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "URL name does not match base of OneDrive file path."
                                    
                                End If
                                
                            End If
                            
                        Next URLInstance
                        
                    Else
                        
                        OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "No mount point found."
                        
                    End If
                    
                    If URLCount = 0 Then
                        OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "No URLs were found."
                    End If
                    
                Next RegistryKey
                
                ' Return the confirmed file path if a valid path was found - if the second pass finds nothing then the first file found in the first pass is kept
                If Len(ConfirmedFilePath) > 0 Then
                    If ReturnFolderPathOnly And Not ExistingFolder(ConfirmedFilePath) And InStr(ConfirmedFilePath, "\") > 0 Then
                        Result = Left(ConfirmedFilePath, InStrRev(ConfirmedFilePath, "\") - 1)
                    Else
                        Result = ConfirmedFilePath
                    End If
                ElseIf Len(Result) > 0 Then
                    OneDriveLocalFilePath_WriteDebuggingLog Log
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "No local path was found in the second pass so the first file found in the first pass is used."
                Else
                    OneDriveLocalFilePath_WriteDebuggingLog Log
                    OneDriveLocalFilePath_WriteDebuggingLog Log, 1, "No local path was found."
                End If
                
            Else
                
                OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "No registry entries were found."
                
            End If
            
            If ExistsCount < 2 Then Exit For
            
            If Pass = 1 Then
                OneDriveLocalFilePath_WriteDebuggingLog Log
                OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "More than one existing file was found in the first pass."
                OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Doing another pass and adding an extra check for a team site with matching share drive base folder."
            End If
            
        Next Pass
        
    Else
        ' The path is not a URL so return it as-is
        If ExistingFile(OneDriveFilePath) Then
            OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Provided path name is not a URL and was found."
            If ReturnFolderPathOnly And InStr(OneDriveFilePath, "\") > 0 Then
                Result = Left(OneDriveFilePath, InStrRev(OneDriveFilePath, "\") - 1)
            Else
                Result = OneDriveFilePath
            End If
        ElseIf ExistingFolder(OneDriveFilePath) Then
            OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Provided folder path is not a URL and was found."
            Result = OneDriveFilePath
        Else
            OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "Provided path name is not a URL and was not found."
        End If
    End If
    
    ' Return the original path or an empty string if no existing file was found
    If Len(Result) = 0 Then
        If Not ReturnEmptyIfFileNotFound Then
            Result = OneDriveFilePath
        End If
        ErrorMessage = "A local file path to an existing file was not found."
    End If
    
    OneDriveLocalFilePath_WriteDebuggingLog Log
    OneDriveLocalFilePath_WriteDebuggingLog Log, 0, "OneDrive result: " & Result
    
    #If Debugging Then
        If Shell Is Nothing Then
            Set Shell = CreateObject("WScript.Shell")
        End If
        LogFilePath = Shell.SpecialFolders("Desktop") & "\" & "OneDrive Local File Path.txt"
        If ExistingFile(LogFilePath) Then
            Kill LogFilePath
        End If
        FileNumber = FreeFile
        Open LogFilePath For Binary Access Read Write Lock Read Write As FileNumber
        Put FileNumber, , Log
        Close FileNumber
    #End If
    
    OneDriveLocalFilePath = Result

End Function


Private Function OneDriveLocalFilePath_DecodeCaretEscapes( _
        ByVal EncodedText As String _
    ) As String

' Replaces the caret escape codes found in personal OneDrive URLs with the characters they stand
' for. Unknown codes are left unchanged. Internal use only.
'
' EncodedText - The URL path text to decode.
'
' Notes:
'
' The known codes are ^0 for &, ^1 for %, ^F for ~, ^J for a comma, ^L for ^, ^M for +, and ^N
' for #. Spaces and the characters ' ; ! @ $ = ` ( ) [ ] { } are not escaped.

    Dim Result As String
    Dim Position As Long
    Dim Character As String
    Dim Code As String
    
    Position = 1
    Do While Position <= Len(EncodedText)
        
        Character = Mid(EncodedText, Position, 1)
        Code = Mid(EncodedText, Position + 1, 1)
        
        If Character = "^" And Len(Code) > 0 Then
            Select Case Code
                Case "0"
                    Result = Result & "&"
                Case "1"
                    Result = Result & "%"
                Case "F"
                    Result = Result & "~"
                Case "J"
                    Result = Result & ","
                Case "L"
                    Result = Result & "^"
                Case "M"
                    Result = Result & "+"
                Case "N"
                    Result = Result & "#"
                Case Else
                    Result = Result & Character & Code
            End Select
            Position = Position + 2
        Else
            Result = Result & Character
            Position = Position + 1
        End If
        
    Loop
    
    OneDriveLocalFilePath_DecodeCaretEscapes = Result

End Function


Private Function OneDriveLocalFilePath_FileExists( _
        ByRef ProposedFilePath As String, _
        ByRef ConfirmedFilePath As String, _
        ByRef ExistsCount As Long, _
        ByRef Log As String _
    ) As Boolean

' Tests if a file or folder path exists. The first path found is kept as the confirmed path and
' every distinct path found is counted. Internal use only.
'
' ProposedFilePath - The local file or folder path to test.
'
' ConfirmedFilePath - Receives the proposed path if it exists and no path has been confirmed yet.
'
' ExistsCount - Incremented each time a distinct existing path is found.
'
' Log - The debugging log.

    OneDriveLocalFilePath_WriteDebuggingLog Log, 2, "Checking proposed file path:", ProposedFilePath
    
    If StrComp(ProposedFilePath, ConfirmedFilePath, vbTextCompare) = 0 Then
        OneDriveLocalFilePath_WriteDebuggingLog Log, 3, "File path has already been confirmed."
        OneDriveLocalFilePath_FileExists = True
        Exit Function
    End If
    
    If ExistingFile(ProposedFilePath) Or ExistingFolder(ProposedFilePath) Then
        If Len(ConfirmedFilePath) = 0 Then
            ConfirmedFilePath = ProposedFilePath
        End If
        ExistsCount = ExistsCount + 1
        OneDriveLocalFilePath_WriteDebuggingLog Log, 3, "File path exists."
        OneDriveLocalFilePath_FileExists = True
    Else
        OneDriveLocalFilePath_WriteDebuggingLog Log, 3, "File path does not exist."
    End If

End Function


Private Sub OneDriveLocalFilePath_WriteDebuggingLog( _
        ByRef Log As String, _
        Optional ByVal Indent As Long, _
        Optional ByVal Message1 As String, _
        Optional ByVal Message2 As String _
    )

' Logs message to debugging log. Internal use only.

    Const IndentSpace As Long = 2
    Const SecondMessagePosition As Long = 55
    
    Dim SpaceCount As Long
    
    #If Not Debugging Then
        Exit Sub
    #End If
    
    If Len(Message1) = 0 Then
        Log = Log & vbCrLf
    Else
        If Len(Message2) > 0 Then
            SpaceCount = SecondMessagePosition - ((Indent * 2) + Len(Message1) + 1)
            If SpaceCount > -1 Then
                Message2 = Space(SpaceCount) & Message2
            Else
                Message2 = Space(1) & Message2
            End If
        End If
        If Len(Log) > 0 Then
            Log = Log & vbCrLf
        End If
        Log = Log & Space(Indent * IndentSpace) & Message1 & Message2
    End If

End Sub


Public Function DecodeURL( _
        ByVal EncodedText As String _
    ) As String

' Decodes percent-encoded characters in a URL, including multi-byte UTF-8 sequences such as accented
' characters, and returns the result.
'
' EncodedText - The URL text to decode.

    Dim Result As String
    Dim Position As Long
    Dim Character As String
    Dim HexDigits As String
    Dim ByteValue As Long
    Dim CodePoint As Long
    Dim RemainingBytes As Long
    
    Position = 1
    Do While Position <= Len(EncodedText)
        
        Character = Mid(EncodedText, Position, 1)
        HexDigits = Mid(EncodedText, Position + 1, 2)
        
        If Character = "%" And HexDigits Like "[0-9A-Fa-f][0-9A-Fa-f]" Then
            
            ByteValue = CLng("&H" & HexDigits)
            Position = Position + 3
            
            ' Single bytes are plain characters, lead bytes start a multi-byte sequence, and continuation bytes add six bits each to the code point
            If ByteValue < &H80 Then
                Result = Result & ChrW(ByteValue)
                RemainingBytes = 0
            ElseIf ByteValue < &HC0 Then
                If RemainingBytes > 0 Then
                    CodePoint = CodePoint * &H40 + (ByteValue And &H3F)
                    RemainingBytes = RemainingBytes - 1
                    If RemainingBytes = 0 Then
                        ' Code points above the basic multilingual plane are stored as a surrogate pair
                        If CodePoint > &HFFFF& Then
                            CodePoint = CodePoint - &H10000
                            Result = Result & ChrW(&HD800& + (CodePoint \ &H400)) & ChrW(&HDC00& + (CodePoint And &H3FF))
                        Else
                            Result = Result & ChrW(CodePoint)
                        End If
                    End If
                End If
            ElseIf ByteValue < &HE0 Then
                CodePoint = ByteValue And &H1F
                RemainingBytes = 1
            ElseIf ByteValue < &HF0 Then
                CodePoint = ByteValue And &HF
                RemainingBytes = 2
            Else
                CodePoint = ByteValue And &H7
                RemainingBytes = 3
            End If
            
        Else
            
            Result = Result & Character
            RemainingBytes = 0
            Position = Position + 1
            
        End If
        
    Loop
    
    DecodeURL = 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


Public Function ExistingFolder( _
        ByVal FolderPath As String _
    ) As Boolean

' Returns True if the path is a folder, False otherwise. This routine does not use the Dir technique
' as the Dir function resets any current Dir process.
'
' FolderPath - Full path to the folder to be evaluated.

    Dim Attributes As Long
    
    On Error Resume Next
    Attributes = GetAttr(FolderPath)
    ExistingFolder = (Err.Number = 0) And (Attributes And vbDirectory) <> 0
    Err.Clear

End Function