Add and Update Single Item in Sharepoint List via VBA
16,678
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.
Author by
AElxs
Updated on July 01, 2022Comments
-
AElxs almost 2 years
I am trying to add and/or update single items in a sharepoint list via VBA and I found a similar question: Import Sharepoint 2010 list data from Excel table using VBA
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.
Thanks in advance
Edit: Also I was here: http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems(v=office.12).aspx but i just dont get it
-
click here almost 7 yearsI'm getting a response status of 200 but the item I've added does not exist. What might you suggest?
-
click here almost 7 yearsIt seems as though this was due to required fields not being given in the post. What modifications need to be made to work with multiple required fields?