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,
Open a xlsm project
Open the VBA IDE
Select Tools -> References from the VBIDE menu
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.
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.
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?
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