Error in update changes from Excel to SharePoint using macro vba

Question

I have load the SharePoint online list in excel using following VBA code from here:

Sub ImportListFromSP()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets(1)
    Dim src(1) As Variant
    src(0) = "https://contoso.sharepoint.com/_vti_bin"
    src(1) = "89F90972-FD90-4B04-BCEB-81840A82DA5E"

    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")

End Sub

Above code works fine.

Now, I have created another macro to update changes from table created in excel to SharePoint Online using following VBA code:

Sub ExportList()
   Dim ws As Worksheet
   Dim objListObj As ListObject

   Set ws = ThisWorkbook.Worksheets(1)
   Set objListObj = ws.ListObjects(1)

   objListObj.UpdateChanges xlListConflictDialog //'Its throws error
End Sub

But Update VBA code throws following error on objListObj.UpdateChanges method:

Run-time error '1004':

Application-defined or object-defined error

I am using Microsoft Excel 2010 and also try with all versions later 2010 but it throws same error as above.

Any solution ?

Answer 1

well as you clearly indicated on your comment that its the error generated from objListObj.UpdateChanges. Its clearly stated that:

This method applies only to lists linked to a SharePoint site. If the SharePoint site is not available, an error is generated.

second part is UpdateChanges is depreciated since 2007. Try using rest call instead

or sync the spreadsheet if possible

https://support.office.com/en-gb/article/Synchronize-a-SharePoint-list-with-a-spreadsheet-program-d4337a01-2be4-43b5-bb42-28aefe64eafd

Answer 2

I have a hunch you want to build the listobject with a good clear definition, then use the stored definition to make your query. I was not able to get it to work right, so I did my solution in straight VBA. I wanted my spreadsheet to always be authoritative and my data was well structured. I used a web service call from VBA to do my updates.

Syncing items between the list and workbook would require reading the list, comparing to the workbook, adding any new items, then comparing the workbook to the list and making an update like below.

This can absolutely be done using straight web services, but it may take some finesse to get the field names correct and updates to the process become more difficult.

Here is the code I used:

Sub UpdateListItems(cUpdates As Dictionary, ByVal ListName As String, SiteURL As String)

'Set and Instantiate our working objects
Set objHttp = CreateObject("MSXML2.XMLHTTP")
sURL = SiteURL & "/_vti_bin/lists.asmx"

Dim node_listName As IXMLDOMElement
Dim node_updates As IXMLDOMElement
Dim node_batch As IXMLDOMElement

Dim xmlUpdateShell As New DOMDocument
xmlUpdateShell.LoadXML "<x:Envelope xmlns:x=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:soa=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
  "<x:Header/><x:Body><soa:UpdateListItems><soa:listName>" & ListName & "</soa:listName><soa:updates><Batch OnError=""Continue""/></soa:updates></soa:UpdateListItems></x:Body></x:Envelope>"

 Set node_updates = xmlUpdateShell.SelectSingleNode("/x:Envelope/x:Body/soa:UpdateListItems/soa:updates/Batch")

For Each i In cUpdates
    If cUpdates(i).SPAction <> "None" Then

        Dim Method_node As IXMLDOMElement
        Set Method_node = xmlUpdateShell.createElement("Method")
            node_updates.appendChild Method_node
            Method_node.setAttribute "ID", cUpdates(i).ID
            Method_node.setAttribute "Cmd", cUpdates(i).SPAction


        Dim Field_node As IXMLDOMElement
        Set Field_node = xmlUpdateShell.createElement("Field")
            Method_node.appendChild Field_node
            Field_node.setAttribute "Name", "ID"
            If cUpdates(i).SPAction <> "New" Then
                Field_node.appendChild xmlUpdateShell.createTextNode(cUpdates(i).SPItem)
            Else
                Field_node.appendChild xmlUpdateShell.createTextNode("New")
            End If

        If cUpdates(i).SPAction <> "Delete" Then
            Set Field_node = xmlUpdateShell.createElement("Field")
                Method_node.appendChild Field_node
                Field_node.setAttribute "Name", "CustomerID"
                Field_node.appendChild xmlUpdateShell.createTextNode(cUpdates(i).ID)

            Set Field_node = xmlUpdateShell.createElement("Field")
                Method_node.appendChild Field_node
                Field_node.setAttribute "Name", "Title"
                Field_node.appendChild xmlUpdateShell.createTextNode(cUpdates(i).Name)
        End If

    End If
Next i

'we invoke the web service
'use this code snippet to invoke a web service which requires authentication
 objHttp.Open "POST", sURL, False
 objHttp.SetRequestHeader "Content-Type", "text/xml"
 objHttp.SetRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

 objHttp.send xmlUpdateShell.XML
 'MsgBox objHttp.responseText

End Sub