2017年的答案
不確定還能不能 work
加減參考看看囉...

'reads the value for the registry key i_RegKey
'if the key cannot be found, the return value is ""
Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function



' This is needed to get the local path, not the one drive path
Private Function GetDocLocalPath(docPath As String) As String
'return the local path for doc, which is either already a local document or a document on OneDrive
Const strcOneDrivePart As String = "https://d.docs.live.net/"
Dim strRetVal As String, bytSlashPos As Byte
    
    strRetVal = docPath & "\"
    If Left(LCase(docPath), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
        'locate and remove the "remote part"
        bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
        strRetVal = Mid(docPath, bytSlashPos)
        'read the "local part" from the registry and concatenate
        strRetVal = RegKeyRead("HKEY_CURRENT_USER\Environment\OneDrive") & strRetVal
        strRetVal = Replace(strRetVal, "/", "\") 'slashes in the right direction
        strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
    End If
    GetDocLocalPath = strRetVal
    
End Function 

右鍵OneDrive icon-->設定-->Office 關閉"使用Office應用程式同步我開啟的Office檔案"

可以使用以下模組便可解決問題。
https://github.com/cristianbuse/VBA-FileTools