How do I get my VBA project reference to an Excel Workbook on OneDrive to use the local drive path rather than the OneDrve URL Path?

Question

Update 2021-03-20: I found that even if I copy the file, the one I want to reference, from my OneDrive to a local folder that's not part of OneDrive, and reference it, the same thing happens. It's only after I also rename the file that I can reference it without its path turning into a URL (assumedly pointing to my online OneDrive). This doesn't serve my needs. I'm trying to find a way to share my VBA code libraries across different apps in different locations on different devices. I may create a separate question on this if I don't figure this out.

Original Question: When I add a reference (in VBA, Tools->References) to an Excel xlsm file that is stored on my local copy of OneDrive, the path gets converted into a url and I can no longer load my VBA project without getting an error that it can't find the file. How can I make the reference ALWAYS point to my local sync'ed OneDrive path?

For example,

1. Open a xlsm project

2. Open the VBA IDE

3. Select Tools -> References from the VBIDE menu

4. Browse to add a reference to another xlsm file.
   a. E.g., C:\Users\Andbio\OneDrive\Code Libraries\RYTEwayCode (XLSM).xlsm

   b. Make sure type xlsm is selected in the "Add Reference" File Open dialog that opens.

   c. select the xlsm file you want to reference and click "Open".

   d. Notice in the "Location:" field at the bottom of the "References" dialog, it shows the local path.

5. Click Ok to add the new reference to the project.

At this point, everything works fine and you can execute code in the file you just referenced. So, the reference works.

6. Select Tools -> References, again, from the VBIDE menu
7. Select the reference you just added.
8. Notice, now, in the "Location:" field that the local path to your referenced file has been replace with a URL. In my case, it now says: "https://d.docs.live.net/8e13263ac9cf0594/Code Libraries/RYTEwayCode (XLSM).xlsm".

If I now save and close the XLSM file, and try to reopen it, I get an error saying it can't find the file at the URL path it showed in step 8 above. I had to reopen it in safemode (/s) to get the file opened again to remove the reference.

I know why this happens and why it was designed this way, I just need a way to get around it if there is one. Is there while still being able to store my referenced file on OneDrive and NOT storing my XLSM file on the same OneDrive?

Answer 1

When you add a reference to a XLSM file, VBA will basically open the workbook as if you used the Open File command. So what you can do is to open the second workbook when the Open event of the first workbook is triggered. You may find issues getting the workbook's physical path with ThisWorkbook.Path if it's saved in a OneDrive folder. I have a helper function that can help you with that as well. Take a look:

Private Sub Workbook_Open()
    
    Workbooks.Open GetWorkbookPath & "\RYTEwayCode (XLSM).xlsm"
    
End Sub

You will also need the GetWorkbookPath function.

Function GetWorkbookPath(Optional wb As Workbook)
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Purpose:  Returns a workbook's physical path, even when they are saved in
    '           synced OneDrive Personal, OneDrive Business or Microsoft Teams folders.
    '           If no value is provided for wb, it's set to ThisWorkbook object instead.
    ' Author:   Ricardo Gerbaudo
    ' Source:   https://github.com/ricardogerbaudo/vba-helpers
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    If wb Is Nothing Then Set wb = ThisWorkbook
    
    GetWorkbookPath = wb.Path
    
    If InStr(1, wb.Path, "https://") <> 0 Then
        
        Const HKEY_CURRENT_USER = &H80000001
        Dim objRegistryProvider As Object
        Dim strRegistryPath As String
        Dim arrSubKeys()
        Dim strSubKey As Variant
        Dim strUrlNamespace As String
        Dim strMountPoint As String
        Dim strLocalPath As String
        Dim strRemainderPath As String
        Dim strLibraryType As String
    
        Set objRegistryProvider = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    
        strRegistryPath = "SOFTWARE\SyncEngines\Providers\OneDrive"
        objRegistryProvider.EnumKey HKEY_CURRENT_USER, strRegistryPath, arrSubKeys
        
        For Each strSubKey In arrSubKeys
            objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "UrlNamespace", strUrlNamespace
            If InStr(1, wb.Path, strUrlNamespace) <> 0 Or InStr(1, strUrlNamespace, wb.Path) <> 0 Then
                objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "MountPoint", strMountPoint
                objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "LibraryType", strLibraryType
                
                If InStr(1, wb.Path, strUrlNamespace) <> 0 Then
                    strRemainderPath = Replace(wb.Path, strUrlNamespace, vbNullString)
                Else
                    GetWorkbookPath = strMountPoint
                    Exit Function
                End If
                
                'If OneDrive Personal, skips the GUID part of the URL to match with physical path
                If InStr(1, strUrlNamespace, "https://d.docs.live.net") <> 0 Then
                    If InStr(2, strRemainderPath, "/") = 0 Then
                        strRemainderPath = vbNullString
                    Else
                        strRemainderPath = Mid(strRemainderPath, InStr(2, strRemainderPath, "/"))
                    End If
                End If
                
                'If OneDrive Business, adds extra slash at the start of string to match the pattern
                strRemainderPath = IIf(InStr(1, strUrlNamespace, "my.sharepoint.com") <> 0, "/", vbNullString) & strRemainderPath
                
                strLocalPath = ""
                
                If (InStr(1, strRemainderPath, "/")) <> 0 Then
                    strLocalPath = Mid(strRemainderPath, InStr(1, strRemainderPath, "/"))
                    strLocalPath = Replace(strLocalPath, "/", "\")
                End If
                
                strLocalPath = strMountPoint & strLocalPath
                GetWorkbookPath = strLocalPath
                If Dir(GetWorkbookPath & "\" & wb.Name) <> "" Then Exit Function
            End If
        Next
    End If
    
End Function

Answer 2

A much lighter hack in 2022 - split the path and change the 'whacks' (/) to backslashes...

    FilePath = ws.Parent.Path & "\" & strN & ".txt"
    
    If LCase(FilePath) Like "http*sharepoint*" Then     ''Sharepoint hack to try to rewrite to local drive path...
        If FilePath Like "http*/Documents/*" Then ''we can take a guess to the local locaiton
            FilePath = Environ$("USERPROFILE") & "\[local OneDrive - pseudo-path]\Documents" & Right(FilePath, Len(FilePath) - 18 - InStr(1, FilePath, "documents/", vbTextCompare))
            FilePath = Replace(FilePath, "/", "\", 1, -1, vbTextCompare)
        Else
            MsgBox "Cannot save to OneDrive, malformed path. OK to cancel", vbCritical + vbOKOnly, "Malformed  path - Onedrive"
        End If
        Stop
        ''redirect to local ...
    End If