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.
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.
<Field Name=" + FieldNameVar + "> with this <Field Name='" + FieldNameVar + "'> (note the added quotes) and it should work.
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
