Search code examples
excelvbasharepointsharepoint-list

Update sharepoint list with VBA

I have a simple script to update sharepoint lists using VBA that's not working.

Option Explicit

Sub add_new_item()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset

Dim mySQL As String

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

mySQL = "SELECT * FROM [Test];"

With cnt
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://share.amazon.com/sites;List={9E4AEE54-AF41-430B-8780-8BD778A1A226};"
        .Open
End With

rst.Open mySQL, cnt, adOpenDynamic, adLockBatchOptimistic

rst.AddNew
        rst!Task_ID = Sheets("Sheet2").Range("C3").Value
rst.Update

'To close the connection and clean the memory

If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing

End Sub

When i try to run it, I get the following error:

enter image description here

And whilst debugging, it happens on this line:

rst.Open mySQL, cnt, adOpenDynamic, adLockBatchOptimistic

I believe that I might have maybe written the wrong connection string?

This is the link to the list: https://share.amazon.com/sites/IPV/Lists/Test/AllItems.aspx This is the list id: List={9E4AEE54-AF41-430B-8780-8BD778A1A226}

Solution

  • The error is weird and not sure if it has something to do with the connections. Upon checking the connection string I think that the correct URL should be "https://share.amazon.com/sites/IPV", also the GUID should be getting on the list setting page not on the list display view.