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

Kevin Jones 7,265 Volunteer Moderator

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

' 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

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

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

105 answers

Kevin Jones 7,265 Volunteer Moderator

There are three possible solutions other than the one posted above that I have seen in various forums:

Using the data files in the AppData\Local\Microsoft\OneDrive folder. This works but involves multiple data files and requires some intricate parsing of data in those files that isn't obvious or easy.
Using FSO or File System Object. This works in some situations but not all.
Using the "Copy local path" function on the Info backstage panel. This is crude and not reliable since we have to use SendKeys and hope the keystroke events are executed in time for our code to get the path from the clipboard.

Back to the solution posted in this thread. I have modified it to produce the log file as a text file in the Desktop folder versus a tab in a workbook. This allows the code to be used in Word or other Office application. While this code, in my opinion, is the most robust of all of the methods identified thus far, it still may have issues and we'll fix them as they are found if possible. The latest version with all known issues resolved is included below.

Kevin

Public Function OneDriveLocalFilePath( _

    Optional ByRef OneDriveFilePath As String, \_

    Optional ByVal ReturnFolderPathOnly As Boolean, \_

    Optional ByVal ReturnEmptyIfFileNotFound As Boolean \_

) 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.

' ReturnFolderPathOnly - Pass True to return the folder path only. This is the equivelant of

' ThisWorkbook.Path. Optional. If omitted then False is assumed.

' ReturnEmptyIfFileNotFound - Pass True to return an empty or null string if the file cannot be

' found, False to return an error message. Optional. If omitted then False is assumed.

' Notes

' Debugging information is written to a text file in the Dektop 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.

Const RegistryPath As String = "HKEY\_CURRENT\_USER\SOFTWARE\SyncEngines\Providers\OneDrive\"

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 LibraryType As String

Dim URLNameSpaceExtended As String

Dim LocalPartialPath As String

Dim PartialPathRootDirectory As String

Dim LocalPartialPathWithFirstFolder As String

Dim LocalPartialPathWithoutFirstFolder 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

Dim FileLength 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

OneDriveLocalFilePath\_WriteDebuggingLog Log, 0, "OneDrive file path: " &amp; OneDriveFilePath

' Determine 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")

    For Pass = 1 To 2

        ExistsCount = 0

        OneDriveLocalFilePath\_WriteDebuggingLog Log, 0, "Evaluating registry entries in '" &amp; RegistryPath &amp; "'"

        ' Enumerate the key HKEY\_CURRENT\_USER\SOFTWARE\SyncEngines\Providers\OneDrive

        If WinMgmtS.EnumKey(&amp;H80000001, Mid(RegistryPath, InStr(RegistryPath, "\") + 1), RegistryKeys, Types) = 0 Then

            EntryCount = 0

            For Each RegistryKey In RegistryKeys

                OneDriveLocalFilePath\_WriteDebuggingLog Log

                ' Each key has three values of interest:

                '

                '   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

                On Error Resume Next

                CID = WScript.RegRead(RegistryPath &amp; RegistryKey &amp; "\CID")

                MountPoint = WScript.RegRead(RegistryPath &amp; RegistryKey &amp; "\MountPoint")

                URLNamespace = WScript.RegRead(RegistryPath &amp; RegistryKey &amp; "\URLNamespace")

                LibraryType = WScript.RegRead(RegistryPath &amp; RegistryKey &amp; "\LibraryType")

                On Error GoTo 0

                EntryCount = EntryCount + 1

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 0, "Evaluating registry entry " &amp; EntryCount

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "Key:", (RegistryKey)

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "CID:", CID

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "URLNamespace:", URLNamespace

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "MountPoint:", MountPoint

                OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "LibraryType:", LibraryType

                ' Remove any trailing slash from the URL name space

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

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

                End If

                ' Determine the extended URL name space which may or may not include the CID

                If Len(CID) = 0 Then

                    URLNameSpaceExtended = URLNamespace

                Else

                    URLNameSpaceExtended = URLNamespace &amp; "/" &amp; CID

                    If Left(OneDriveFilePath, Len(URLNameSpaceExtended)) &lt;&gt; URLNameSpaceExtended Then

                        URLNameSpaceExtended = URLNamespace

                    End If

                End If

                ' Looking for a local file path only if the base of the path being evaluated matches the extended URL name space

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

                    LocalPartialPath = Mid(OneDriveFilePath, Len(URLNameSpaceExtended) + 2)

                    ' It's not always clear if the first directory in the partial path is used locally, it may be a directory only in the cloud to distinguish directories shared by others

                    If InStr(LocalPartialPath, "/") &gt; 0 Then

                        PartialPathRootDirectory = Left(LocalPartialPath, InStr(LocalPartialPath, "/") - 1)

                    Else

                        PartialPathRootDirectory = vbNullString

                    End If

                    If Pass = 1 Or Pass = 2 And LibraryType = "teamsite" And Right(MountPoint, Len(PartialPathRootDirectory)) = PartialPathRootDirectory Then

                        ' Build two local partial paths: one with the first folder after the mount point, and the second without the first folder after the mount point

                        LocalPartialPathWithFirstFolder = Replace(Replace(Mid(OneDriveFilePath, Len(URLNameSpaceExtended) + 2), "/", "\"), "%20", Space(1))

                        LocalPartialPathWithoutFirstFolder = Mid(LocalPartialPathWithFirstFolder, InStr(LocalPartialPathWithFirstFolder, "\") + 1)

                        If LocalPartialPathWithFirstFolder = LocalPartialPathWithoutFirstFolder Then

                            LocalPartialPathWithFirstFolder = vbNullString

                        End If

                        OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "Local partial path without first folder:", LocalPartialPathWithoutFirstFolder

                        OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "Local partial path with first folder:", LocalPartialPathWithFirstFolder

                        If OneDriveLocalFilePath\_FileExists(MountPoint &amp; "\" &amp; LocalPartialPathWithoutFirstFolder, ConfirmedFilePath, ExistsCount, Log) Then Exit For

                        If Len(LocalPartialPathWithFirstFolder) &gt; 0 Then

                            If OneDriveLocalFilePath\_FileExists(MountPoint &amp; "\" &amp; LocalPartialPathWithFirstFolder, ConfirmedFilePath, ExistsCount, Log) Then Exit For

                        End If

                    Else

                        If Pass = 2 Then

                            OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "Not a team site or right part of directory of mount point does not match the first directory of the partial path"

                        End If

                    End If

                Else

                    OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "URL name space does not match base of OneDrive path"

                End If

            Next RegistryKey

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

            If Len(ConfirmedFilePath) &gt; 0 Then

                If ReturnFolderPathOnly Then

                    Result = Left(ConfirmedFilePath, InStrRev(ConfirmedFilePath, "\") - 1)

                Else

                    Result = ConfirmedFilePath

                End If

            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"

            Result = OneDriveFilePath

        End If

        If ExistsCount &lt; 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

    Result = OneDriveFilePath

End If

If ExistsCount = 0 Then

    If Not ReturnEmptyIfFileNotFound Then

        Result = "A local file path to an existing file was not found."

    End If

End If

OneDriveLocalFilePath\_WriteDebuggingLog Log

OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "OneDrive result: " &amp; Result

#If Debugging Then

    LogFilePath = CreateObject("Wscript.Shell").SpecialFolders("Desktop") &amp; "\" &amp; "OneDrive Local File Path Lof.txt"

    On Error Resume Next

    Kill LogFilePath

    On Error GoTo 0

    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_FileExists( _

    ByRef ProposedFilePath As String, \_

    ByRef ConfirmedFilePath As String, \_

    ByRef ExistsCount As Long, \_

    ByVal Log As String \_

) As Boolean

' Tests if file path exists. Internal use only.

OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "Checking proposed file path", ProposedFilePath

If ProposedFilePath = ConfirmedFilePath Then

    OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "File path has already been confirmed"

    Exit Function

End If

If ExistingFile(ProposedFilePath) Then

    ConfirmedFilePath = ProposedFilePath

    ExistsCount = ExistsCount + 1

    OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "File path exists"

Else

    OneDriveLocalFilePath\_WriteDebuggingLog Log, 1, "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 = 44

Dim SpaceCount As Long

#If Not Debugging Then

    Exit Sub

#End If

If Len(Message1) = 0 Then

    Log = Log &amp; vbCrLf

Else

    If Len(Message2) &gt; 0 Then

        SpaceCount = SecondMessagePosition - ((Indent \* 2) + Len(Message1) + 1)

        If SpaceCount &gt; -1 Then

            Message2 = Space(SpaceCount) &amp; Message2

        Else

            Message2 = Message2

        End If

    End If

    If Len(Log) &gt; 0 Then

        Log = Log &amp; vbCrLf

    End If

    Log = Log &amp; Space(Indent \* IndentSpace) &amp; Message1 &amp; Message2

End If

End Sub

0 comments

Kevin Jones 7,265 Reputation points Volunteer Moderator

2024-10-30T21:35:25+00:00

The perusal of the registry has nothing to to with the application running the code. Can you run the code with debugging enabled in an Excel workbook on that machine?

Kevin
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2024-07-03T07:08:24+00:00

Hi Kevin. One final (I hope) thing that arguably might be an improvement:

I will almost exclusively use the code to replace ThisWorkbook.Path, and I have several projects where this is called repeatedly, because other files in the project, created or referenced in different subs and functions in various modules, have paths relative to it, and there is no or little performance loss in using ThisWorkbook.Path every time, rather than saving it once and for all in a global variable, or passing it around as an argument.

Performance is more of an issue with the code here, so my thought is this:

Declare a global string variable, say "SavedThisWorkbookFullpath", and if the OneDriveFilePath argument is empty, initialize it to the value of SavedThisWorkbookFullpath, unless that is empty too, in which case SavedThisWorkbookFullpath and OneDriveFilePath are both initialized to ThisWorkbook.FullName.

I've just timed this in my environment: Your routine runs 0.3 seconds. I wrote a little function that only calls yours if SavedThisWorkbookFullpath is empty; AFTER the first run that calls yours and sets SavedThisWorkbookFullpath, it runs 0.0000001 second. (To wit, a for loop just calling yours 1000 times ran 29.5 seconds; a for loop calling mine 100000001 times ran 11.3 seconds, starting the measurement AFTER the first call.)
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2024-06-13T16:32:48+00:00

Would it be possible for someone to publish the entire module?
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2024-06-01T06:39:31+00:00

I believe the OneDrive problem you are referring to is simply intolerable. I spent hours with the MS Support team trying to convince them of this problem and they simply would not acknowledge it.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments