With the function below you can get the local path to a OneDrive folder, either the "Commercial" or "Consumer" type:

Function OneDrive_LocalFolder(Optional blnPersonal As Boolean = False, Optional blnInclLastPathSepChar As Boolean = True) As String
' updated 2020-06-22 by OPE
' returns the path to the local OneDrive folder, business or personal, returns an empty string if the folder doesn't exist
Dim strOneDrive As String, OK As Boolean
    On Error Resume Next
    If blnPersonal Then
        strOneDrive = Environ("OneDriveConsumer") ' local path to the OneDrive Personal sync folder
    Else
        strOneDrive = Environ("OneDriveCommercial") ' local path to the OneDrive Business sync folder
    End If
    'If Len(strOneDrive) = 0 Then strOneDrive = Environ("OneDrive") ' local path to the OneDrive sync folder
    On Error GoTo 0
    If Len(strOneDrive) = 0 Then Exit Function ' folder information not found
    
    OK = False
    On Error Resume Next
    OK = Len(Dir(strOneDrive, vbDirectory)) <> 0 ' vbDirectory=16
    On Error GoTo 0
    If Not OK Then Exit Function ' folder doesn't exist
    
    If blnInclLastPathSepChar Then ' add any missing last path separator char
        If Right(strOneDrive, 1) <> Application.PathSeparator Then strOneDrive = strOneDrive & Application.PathSeparator
    Else ' remove any existing last path separator char
        If Right(strOneDrive, 1) = Application.PathSeparator Then strOneDrive = Left(strOneDrive, Len(strOneDrive) - 1)
    End If
    OneDrive_LocalFolder = strOneDrive
End Function

Function Workbook_LocalPath(wb As Workbook, Optional blnReturnFolderOnly As Boolean = False) As String
' updated 2020-06-23 by OPE
' returns the local file or folder path to a workbook that is saved in a OneDrive Personal or Business folder
' will not work if the workbook is saved in a shared OneDrive folder
' useful when wb.FullName or wb.Path returns an URL path
' example: strFilePath = Workbook_LocalPath(ActiveWorkbook)
Const cstrOneDrivePersonal As String = "https://*.docs.live.net/*/*"
Const cstrOneDriveBusiness As String = "https://*.sharepoint.com/personal/*/documents/*"
Dim strFilePath As String, strLocalPath As String, strOneDrive As String, i As Long
    If wb Is Nothing Then Exit Function
    
    strFilePath = wb.FullName
    If blnReturnFolderOnly Then
        Workbook_LocalPath = Left(strFilePath, Len(strFilePath) - Len(wb.Name)) ' folder included the last path separator char
    Else
        Workbook_LocalPath = strFilePath ' original file path
    End If
    If Not LCase(strFilePath) Like "https://*" Then Exit Function
    
    strLocalPath = vbNullString
    strOneDrive = vbNullString
    
    If LCase(strFilePath) Like LCase(cstrOneDrivePersonal) Then ' personal or consumer OneDrive path
        On Error Resume Next
        strLocalPath = Split(strFilePath, "/", 5, vbBinaryCompare)(4) ' everything after the 4th path separator
        strOneDrive = Environ("OneDriveConsumer") ' path to the local OneDrive Personal folder
        On Error GoTo 0
    End If
    
    If LCase(strFilePath) Like LCase(cstrOneDriveBusiness) Then ' business or commercial OneDrive path
        On Error Resume Next
        strLocalPath = Split(strFilePath, "/", 7, vbBinaryCompare)(6) ' everything after the 6th path separator
        strOneDrive = Environ("OneDriveCommercial") ' path to the local OneDrive Business folder
        On Error GoTo 0
    End If
    
    If Len(strOneDrive) > 0 Then ' the local OneDrive folder path was found
        strLocalPath = Replace(strLocalPath, "/", Application.PathSeparator) ' convert path separator chars
        If Right(strOneDrive, 1) <> Application.PathSeparator Then strOneDrive = strOneDrive & Application.PathSeparator
        strLocalPath = strOneDrive & strLocalPath
        If Len(Dir(strLocalPath)) > 0 Then ' file exist in the local folder
            If blnReturnFolderOnly Then
                Workbook_LocalPath = Left(strLocalPath, Len(strLocalPath) - Len(wb.Name)) ' folder included the last path separator char
            Else
                Workbook_LocalPath = strLocalPath ' local file path
            End If
        End If
    End If
End Function