Connecting to Excel file stored on SharePoint via ADODB

Question

I'm trying to query data from my workbook located on SharePoint using the following code (which I've been using successfully for years in workbooks stored on my hard drive):

Dim con As Object
Dim rcs As Object
Dim strConnection As String

Set con = CreateObject("ADODB.Connection")
Set rcs = CreateObject("ADODB.Recordset")

strConnection = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
Call con.Open(strConnection)

At this point, the following error is thrown:

[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

In this thread, it was suggested to replace certain characters in the workbook's path, and they also used a different Provider. So I tried the following, which is pretty much copy pasted (and slightly cleaned up) from the last comment in that thread:

Private Sub ConnectToDatabase()
    Dim con As Object
    
    Dim strPath As String, strCon As String
    
    strPath = Convert_HTTP_To_NetworkPath(ThisWorkbook.FullName)
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    Set con = CreateObject("ADODB.Connection")
    
    Call con.Open(strCon)

End Sub


Function Convert_HTTP_To_NetworkPath(strPath As String) As String
    
    strPath = Replace(strPath, " ", "%20")
    strPath = Replace(strPath, "https://mycompany.sharepoint.com", "\\mycompany.sharepoint.com@SSL\DavWWWRoot")
    strPath = Replace(strPath, "/", "\")
    If IsIn(".", Right(strPath, 5)) = False And Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    Convert_HTTP_To_NetworkPath = strPath
    
End Function

Function IsIn(strKeyword As String, strText As String)

    If UCase(strText) Like "*" & UCase(strKeyword) & "*" Then IsIn = True Else IsIn = False
    
End Function

Resulting in this error:

Method 'Open' of object '_Connection' failed.

Using my original connection string (with the cleaned workbook path) only brought back the first error.

Following the advice in this thread, I also tried this connection string:

strCon = "Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" & strPath & "; Extended Properties='Excel 16.0 XLSM; HDR=Yes; IMEX=1';"

Which yieled yet another error:

Could not find installabe ISAM.

The workbook path does not contain any special characters, and the file is saved as xlsm.