I have found the below code from this site:
Add records to SharePoint list from Excel VBA using shared link

It seems to work but I get an error of “Excel VBA Automation Error Exception Occurred” and it closes my Excel 365 down. I’ve searched MS and found https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vba-automation-error-help/7ccb46af-ea45-459b-91e3-66c614425cbf which says to add a reference to AccessibilityCplAdmin 1.0 Type Library which I did but I’m still getting this error. I’ll contact MS as well, but if anyone has any ideas why I’d be getting this error or how to fix it, please let me know. Thanks.

Below is my code, and I have validated my DATABASE connection string:

Sub AddItem()
' Requires a reference to "Microsoft ActiveX Data Object 6.0 Libray" to insert a record into a sharepoint list "AccessLog"
' Requires a reference to AccessibilityCplAdmin 1.0 Type Library
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim mySQL As String

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
   ‘Renewal is the name of my table
    mySQL = "SELECT * FROM [Renewal];"

    With cnt ' See https://www.connectionstrings.com/sharepoint/

'Writes only
.ConnectionString = _
      "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" _
    & "DATABASE=http://mysharepointsite.com/documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698};"
        .Open
    End With

    rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

    rst.AddNew
        rst.Fields("RequestType") = "RequestType"
        rst.Fields("Group") = "Group"
        rst.Fields("Requester") = "Requester"
        rst.Fields("RequestDate") = "RequestDate"
        rst.Fields("Unit") = "Unit"
        rst.Fields("Full VIN") = "Full VIN"
        rst.Fields("State") = "State"
        rst.Fields("Plate Number") = "Plate Number"
        rst.Fields("Vehicle needs sticker (X)") = "Vehicle needs sticker (X)"
        rst.Fields("Vehicle needs plate (X)") = "Vehicle needs plate (X)"
        rst.Fields("Comments") = "Comments"
    rst.Update ' commit changes to SP list

    If CBool(rst.State And adStateOpen) = True Then rst.Close
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    
    
    MsgBox "Your submission has been received."
End Sub

I just need assistance with the error of “Excel VBA Automation Error Exception Occurred”. Why is it occurring and how do I fix it?