Use Excel-VBA (MSXML2.XMLHTTP object) to update SharePoint List

Question

General Info.: I need to update (add/edit) a SharePoint list from Excel. I was able to do it with a ListObject, however this is not the direction we are leading to.

After reading through all Google possibilities, I came up (maybe I'm wrong) with the idea to use MSXML2.XMLHTTP object to update the SharePoint List.

Current Problem: My code below runs through untill the end, I get objXMLHTTP.Status = 200, but the SharePoint List is not updated.

Code

Option Explicit

Const SharepointUrl As String = "http://share.corning.com/sites/ipp/PMOSandbox/"
Const ListName As String = "{60CE6622-D25B-447A-BFBF-8F3DD5B9FCF0}"
Const VIEWNAME As String = "{91ADBAE5-479F-4C80-A5FF-8EDA7A233B82}"

Sub Add_Item()

Dim objXMLHTTP As MSXML2.XMLHTTP    
Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String    
Dim ValueVar As String, FieldNameVar As String

Set objXMLHTTP = New MSXML2.XMLHTTP

FieldNameVar = "IPP #"
ValueVar = "Shai"

'Add New Item'    
strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='New'><Field Name='" + FieldNameVar + "'>1004</Field>" + _
          "<Field Name='Title'>Uploaded from VBA</Field>" + _
          "<Field Name='Next KD Status'>" + ValueVar + "</Field>" + _
          "</Method></Batch>"

objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False
objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
 & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
 & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
 & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & ListName _
 & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

objXMLHTTP.send strSoapBody

Do
    ' wait for response
Loop Until objXMLHTTP.Status = 200

Set objXMLHTTP = Nothing

MsgBox "Finished Running !"

End Sub

Answer 1

Here is a basic outline of how to do this with ADO.

Option Explicit

Public Sub Update()
On Error GoTo ErrHand

    'Create the connection object with ADO
    Dim conn        As Object: Set conn = CreateObject("ADODB.Connection")

    'Write your update statement
    Dim sql         As String
    sql = "Update MyTable set Field1= 'A', Field2='B' Where MyID=1234"

    'Open the connection and submit the update
    'In my experience, credentials should be requested by the server -
    'by way of a windows pop-up
    With conn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
                            "DATABASE=MySharePointURL;" & _
                            "LIST={MyListGUID};"
        .Open
        .Execute sql
    End With

CleanExit:
    If conn.State = 1 Then conn.Close: Set conn = Nothing
    Exit Sub

ErrHand:
    Debug.Print Err.Number, Err.Description
    GoTo CleanExit
End Sub