Add item to SharePoint list

Question

How do I add an item from an Excel file to a SharePoint list?

I cannot use Access as an additional step here.

Within Excel 2019 I run a macro and part of it should be uploaded to a SharePoint list for later purposes / workflow.

I am running into error

"could not find installable ISAM".

Here is part of my code which is creating the error. I found some posts that single quotations can solve the problem, but it didn't work out for me.
Also it seems an Access reference is required - I downloaded the AccessDatabaseEngine.

With cnt
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
        "DATABASE=" & mySPsite & ";LIST=" & myGuid & ";"
    .Open
End With

Answer 1

This worked for me to read/update a SharePoint list named "TestList" which has a single user-defined column "Title":

Sub SharePointListUpdateAndRead()
    
    Const mySPSite As String = "https://company.sharepoint.com/sites/mySite"
    Const myGUID As String = "{gh77r2j4-xxxx-4890-8999-3531bde50g5y}"
    
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, lngRecs As Long
    
    Set cn = SPListConnection(mySPSite, myGUID, True)       'open a "write" connection
    cn.Execute "insert into TestList(Title) values('Aardvark stories') ", lngRecs 'run an update
    Debug.Print "Records affected", lngRecs
    cn.Close
    
    Set cn = SPListConnection(mySPSite, myGUID) 'open a "read" connection
    Set rs = cn.Execute("select * from testlist where title like '%stories%'")
    ListRecords rs
    cn.Close
    
End Sub


'Return an open connection to the specified site/list guid
'Connection defaults to Read-only
'  Pass `True` to `writeable` if you want to use the connection for updates
Function SPListConnection(spSite As String, spListGuid As String, _
              Optional writeable As Boolean = False) As ADODB.Connection
              
    Dim cn As New ADODB.Connection
    With cn
        .ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=" & _
            IIf(writeable, 0, 1) & ";RetrieveIds=Yes;" & _
            "DATABASE=" & spSite & ";LIST=" & spListGuid & ";"
        .Open
    End With
    Set SPListConnection = cn
End Function

'List out records to the Immediate pane
Sub ListRecords(rs As ADODB.Recordset)
    Dim f As ADODB.Field
    If Not rs.EOF Then
        Do While Not rs.EOF
            Debug.Print "--------"
            For Each f In rs.Fields
                Debug.Print f.Name & " = " & f.Value
            Next f
            rs.MoveNext
        Loop
    Else
        Debug.Print "no records found"
    End If
End Sub

Connection strings from https://www.connectionstrings.com/sharepoint/