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

Kevin, Thank you for this work around to a very frustrating situation, especially for someone like me who has just started to work with VBA.

Is there a way to include this in all Spreadsheets that I create at work going forward so I don't have to add this each time.

Also, again, I'm really new at this, what is the correct function to call the routine with? what do I replace Optional ByRef OneDriveFilePath As String with?

Public Function OneDriveLocalFilePath(Optional ByRef OneDriveFilePath As String) As String

Note that I am posting below a revised version of the routine above that supports returning the correct path for ThisWorkbook.Path as well as ThisWorkbook.FullName.

The parameter OneDriveFilePath is only needed if you are looking for the local path of a workbook other than the one in which the code resides and if that workbook also resides in a OneDrive managed folder. Omitting it from the call results in it being assumed to be ThisWorkbook.FullName.

If you do want to specify the parameter for documentation purposes, then your call would look like:

TheWorkbookPathIWishMicrosoftGaveMeInTheFirstPlace = OneDriveLocalFilePath(ThisWorkbook.FullName)

Or, if looking for the local path of another workbook:

TheWorkbookPathIWishMicrosoftGaveMeInTheFirstPlace = OneDriveLocalFilePath(Workbooks("Another Workbook.xlsx").FullName)

As far as how and when to call this routine, that depends on what your objective is. In my case, I need it when looking for folders and files relative to the workbook in which I am working. If my project is looking for some other file not in a place relative to the project workbook location, then I will use the folder or file selection dialog with user interaction and use that path.

Regarding the relative path problem that OneDrive has presented to us, this is a very common problem in a shared environment where different users on different machines are using the same workbook and the folder/file structure is the same for everyone except for the root folder path which often has their name and/or their company name embedded in the path. For example, if we're looking for a file in the same folder as our workbook, we would do something like this:

Workbooks.Open ThisWorkbook.Path & "\Another Workbook.xlsx"

That will work in any location other than a folder managed by OneDrive. But, inside a OneDrive managed folder, we now have this crap:

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

and, as you can imagine, it fails miserably.

Using my revised routine below, we can now do this:

Workbooks.Open OneDriveLocalFilePath(ThisWorkbook.FullName, True) & "\Another Workbook.xlsx"

And it will work whether or not our workbook is in a shared directory.

From your question it seems as though you want to determine a workbook's correct FullName and Path properties but without adding the below VBA code to each and every workbook. The solution is an Add-In that monitors all of the workbooks opened and pushes the correct FullName and Path properties into those workbooks as a name or cell value. This is an interesting idea but it has a caveat: doing this will mean that other people opening your shared workbook will not have access to these properties unless they employ the same solution on their machines. Since you will only need these properties if you are creating macro enabled workbooks, then I recommend not doing this and just including the code in every macro enabled project. I encourage you to start creating library modules that you include with every project. These library modules contain code you write that is generic and can be repurposed from project to project, saving you development time as your library grows.

That said, there is one application where you might not have any VBA code and yet still need the correct FullName and Path properties: Power Query queries that reference a source in a location relative to the main workbook. If this is the case, then, yes, an Add-In that loads the opening workbook with the correct FullName and Path properties would work but the same caveat mentioned above still applies: everyone using the workbook has to have the same Add-In installed.

Kevin

Public Function OneDriveLocalFilePath( _

    Optional ByRef OneDriveFilePath As String, \_ 

    Optional ByVal ReturnFolderPathOnly 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.

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 

        If ReturnFolderPathOnly Then 

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

        Else 

            Result = ConfirmedFilePath 

        End If 

    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

Kevin,

Please excuse my ignorance, again I'm just learning VBA and the stuff you are doing is well past where I'm at. That said I was able to add your code to the project I'm working on at work and it works perfectly. Thank you!!!!

I'm not sure if I understand what you've written above but I understand the part of the add-in and probably won't go in that direction. I will just keep a copy of the subroutine and add when and if needed.

As for your modification is this so that if I run the code at work in a business OneDrive environment where file names include web addresses and it will also work in environments where the file names are not web based such as C:\directory\filename?

Yes. Note these lines of code:

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

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

...

Else

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

Result = OneDriveFilePath

End If

This code looks at the path passed in (or ThisWorkbook.FullName is omitted) and determines if it's a URL or a local path. If a URL - the only time I've seen a URL is when the workbook resides in a OneDrive or ShareDrive folder - then it runs through the process of trying to determine the local path using settings in the registry. If that fails then the original path - the URL - is returned. If a local path, that path is returned as is.

I have found an issue with the above code: it fails if the workbook's parent folder is no more than one level down in the folder hierarchy from the mount point location. I'll post an update when I get it resolved.

Kevin

Kevin, Not enough ways to say thnk you.

I just tried the new code you provided and I got an error message

                If ExistingFile(ProposedFilePath) Then 

                    ConfirmedFilePath = ProposedFilePath 

                    Exit For