Handling the Obfuscation of ThisWorkbook.Path and .FullName for OneDrive and Sharepoint Files
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.xlsxPut that workbook in any OneDrive shared folder, and this is returned:
https://datapros-my.sharepoint.com/personal/kjones_dataautopros_com/Documents/Just Another Workbook.xlsxwhere the actual local path is:
C:\Users\User Name\OneDrive - Data Automation Professionals\Just Another Workbook.xlsxRight 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 FunctionFeedback
Question, correction, or comment? Let us know.