Issue

The backend of OneDrive is Sharepoint. This is mostly hidden on the desktop. When one interacts with files stored in a OneDrive folder Microsoft makes it appear that files and folders behave like anyother file or folder stored locally on your computer.

However,  within Office applications if you examine the file path instead of "c:\..." you see "https://..."

Again for most users this is not a problem, because Microsoft makes looking at and working with OneDrive files the same as files store locally.

However, this is not the case if you are trying to access those files via VBA. You will not be able to open or save files.

Research on the Internet reveals that many others have encountered this issue and the resolutions and solutions are either incomplete, confusing or both.

Solution

The solution to being able to open and save files stored on OneDrive via VBA requires reading from the Windows Registry, which uses some rather opaque system calls. It also requires specific calls to different functions based upon the type of data being accessed, something I spent several hours trying to figure out.

The goal is to convert a OneDrive path e.g.:

https://something-my.sharepoint.com/personal/user_site_com/Documents/folder1

To:

c:\Users\MyUserName\OneDrive - CompanyName\folder1

To do this we need to know the ServiceEndpointUri and the UserFolder. These values can be found in the registry in:

HKEY_CURRENT_USER\Software\Microsoft\OneDrive\Accounts\Business1

Here is the code:

Option Explicit

'-----------------------------'
' Dependencies - CWMCRegistry '
'-----------------------------'

Public Function ConvertOneDrivePathToLocalPath(ByVal sOneDrivePath As String) As String

   Dim sLocalPath As String
   Dim sRemotePath As String
   Dim sTemp As String
   
   Dim poReg As New CWMCRegistry
   
   poReg.RegistryFolder = "Software\Microsoft\OneDrive\Accounts\Business1"
   
   poReg.RegistryKey = "ServiceEndpointUri"
   sRemotePath = poReg.Value
   
   poReg.RegistryKey = "UserFolder"
   sLocalPath = poReg.Value
    
   '------------------------'
   ' Remove trailing "_api" '
   '------------------------'
   sRemotePath = Left(sRemotePath, Len(sRemotePath) - 4)
   
   '-----------------------------------------------------------'
   ' Remove ServiceEndPointUri and Documents from OneDrivePath '
   '-----------------------------------------------------------'
   sTemp = Mid(sOneDrivePath, Len(sRemotePath) + Len("Documents/") + 1, Len(sOneDrivePath))
   
   '-------------------------------------------'
   ' Replace forward slashes with back slashes '
   '-------------------------------------------'
   sTemp = Replace(sTemp, "/", "\")
   
   '----------------------------------------------------------'
   ' Append actual path in OneDrive to local path of OneDrive '
   '----------------------------------------------------------'
   ConvertOneDrivePathToLocalPath = sLocalPath & "\" & sTemp
    
End Function

CWMCRegistry Class

Note: This class was designed to provide general access to the Windows Registry and I only implemented the functionality I needed, so for the general case of accessing any registry key in any registry hive a little bit more work is required.

For example, CWMCRegistry class only reads from HK_CURRENT_USER and it is only able to read REG_SZ (string) keys.

This webpage (https://learn.microsoft.com/en-us/previous-versions/windows/desktop/regprov/stdregprov) documents StdRegProv system call and should provide enough information to modify the code below to suite your needs.

Option Explicit

Private msFolder As String
Private msKey As String

Private mvRegKeys As Variant
Private mvRegTypes As Variant

Private Const REGOBJECT = "winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv"

Private Const HKEY_CURRENT_USER = &H80000001
' Private Const HKEY_LOCAL_MACHINE = &H80000002 ' Not used at this time

'----------------'
' Registry Types '
'----------------'
Private Const REG_SZ = 1
Private Const REG_EXPAND_SZ = 2
Private Const REG_BINARY = 3
Private Const REG_DWORD = 4
Private Const REG_MULTI_SZ = 7

Public Property Get RegistryFolder() As String
   RegistryFolder = msFolder
End Property

Public Property Let RegistryFolder(ByVal sFolder As String)
   msFolder = sFolder
End Property

Public Function Value() As Variant
   
   Dim RegKeys As Variant
   Dim v As Variant
   Dim i As Integer
   Dim s As String
   
   Dim ro As Object
   
   v = ""
   Set ro = GetObject(REGOBJECT)
   ro.EnumValues HKEY_CURRENT_USER, Me.RegistryFolder, mvRegKeys, mvRegTypes
   
   For i = 0 To UBound(mvRegKeys)
      If CStr(mvRegKeys(i)) = msKey Then
      
         Select Case mvRegTypes(i)
            
            Case REG_SZ
               ro.GetStringValue HKEY_CURRENT_USER, msFolder, CStr(mvRegKeys(i)), v
               Value = v
               Exit Function
               
            Case REG_EXPAND_SZ
               MsgBox "REG_EXPAND_SZ Not Implemented"
            
            Case REG_BINARY
               MsgBox "REG_BINARY Not Implemented"
            
            Case REG_DWORD
               ro.GetDWORDValue HKEY_CURRENT_USER, msFolder, msKey, v
               Value = v
               Exit Function
            Case REG_MULTI_SZ
               MsgBox "REG_MULTI_SZ Not Implemented"
            
         End Select
      End If
   Next
      
   Value = v
End Function

Public Property Get RegistryKey() As String
   RegistryKey = msKey
End Property

Public Property Let RegistryKey(ByVal sKey As String)
   msKey = sKey
End Property