1

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 '18 at 9:34
1
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
1

just to build upon the above answer (which is great, thank you!) and to account for jdgregson's correction. Please see the below code, there are some comments in there which will show you how to update an item as well as creating new ones. (I would have made this a comment on the answer but don't have the reputation).

Sub Main()

Dim ListName As String
Dim SharepointUrl As String
Dim ValueVar As String
Dim FieldNameVar As String

ListName = "test"
SharepointUrl = "http://example/"  'for a subsite it would be: http://example/exampleSubsite/
ValueVar = "TestValue"
FieldNameVar = "Title"


Call Add_Item(ListName, SharepointUrl, ValueVar, FieldNameVar)

End Sub


Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

Dim objXMLHTTP As Object

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

Set objXMLHTTP = CreateObject("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>"

'Update Existing Items
'strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='Update'><Field Name='ID'>**Insert ID number here**</Field><Field Name='" + FieldNameVar + "'>" + ValueVar + "</Field></Method></Batch>"

'other method information https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-services/ms774660(v%3Doffice.12)

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
    Debug.Print "Happy Days!"
End If

Set objXMLHTTP = Nothing

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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