I am trying to add and/or update single items in a SharePoint list via VBA and I found a similar question:

But I have to say that I can only delete items with this code and I am not familiar with this code to understand how I add.

Edit: Also I was here: Lists.UpdateListItems Method, but I just don't get it.

  • I try to use this code to update SharePoint list item - the code can run without error ... but no new item show up in the list. Note : i'm using office 365 right now. – chatchavadits Aug 10 at 9:34
Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

Dim objXMLHTTP As MSXML2.XMLHTTP

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP

strListNameOrGuid = ListName


'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + 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>" & strListNameOrGuid _
 & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

objXMLHTTP.send strSoapBody

If objXMLHTTP.Status = 200 Then
' Do something with response
End If

Set objXMLHTTP = Nothing

End Sub

Now I got it. This is how you can Add items to a SharePoint list. FieldNameVar is the name of a Field you have to put something in (for example could this Value be 'Title') and ValueVar is the Value you put in the FieldNameVar field.

  • I spent several hours trying to debug this and get it to work, only to find that your code contains a trivial yet critical error: the Name field is not quoted in the XML output. Replace this bit: <Field Name=" + FieldNameVar + "> with this <Field Name='" + FieldNameVar + "'> (note the added quotes) and it should work. – jdgregson Dec 21 '17 at 22:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.