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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)