4

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

Community
  • 1
  • 1
AElxs
  • 101
  • 1
  • 1
  • 4

1 Answers1

6
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.

AElxs
  • 101
  • 1
  • 1
  • 4
  • I'm getting a response status of 200 but the item I've added does not exist. What might you suggest? – click here May 22 '17 at 13:23
  • It 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? – click here May 22 '17 at 13:30
  • @clickhere I know this is now 6 years late, but for your benefit/anyone else that might come along... You'll get a 200 because the URL (site, list) exist. SP requires that the item exists first in order to update it, so you need to first create it. That's a separate SOAP structure using `CreateListItems`-not `UpdateListItems`. For required fields, this should only matter on creation, unless you are removing (blanking out) values of those kinds of fields on update. On creation you would want to note any required fields and do validation-ensure you have values for those fields-before you submit. – vapcguy Apr 12 '23 at 19:24