Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

i have learned how to gather data from a sharepoint list into Excel using VBA simple macro only.

Now i would like to do the other way around - update some list in my Excel file, and send them back to sharepoint to update the list, using VBA only.

is that possible, and if yes - how?

Thanks!

Yes. You can use the XMLHttpRequest object provided by Microsoft's XML SDK, as well as the UpdateListItems web service provided by SharePoint to update one or more items. Add a reference to "Microsoft XML, v6.0" in the Tools -> References menu in your Visual Basic Editor, and then use something like the code below.

Dim objXMLHTTP As MSXML2.XMLHTTP
Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String
Set objXMLHTTP = New MSXML2.XMLHTTP
strListNameOrGuid = "My List Name or GUID"
' Delete item with internal ID of "1"
strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='Delete'><Field Name='ID'>1</Field></Method></Batch>"
objXMLHTTP.Open "POST", "http://myserver/mysite/_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
You can read more about the syntax of the UpdateListItems and how the batch XML should be structured by going here.
                I managed to enter new data into a sharepoint list via this script, but got issues when tried to update fields which are not sting. Multiline text field didn't work with this script. Do you have an idea what can be the solution?
– user3016795
                Nov 24, 2013 at 11:21
                I have been able to use code like the above with multiline text fields. Perhaps there is something else going on, so you might want to share a code snippet. With regards to other data types, if you have a Lookup field, you need to store the numeric value of that lookup field when setting its value.
– ErinsMatthew
                Nov 25, 2013 at 12:54
                I'm using the following combination to add fields to be populated: '"<Field Name=" & """" & "Number" & """" & ">" & "12345" & "</Field>"' while the "Number" is the column name and the "12345" is the value. If the column name refers to a column which is not regular text column, it is simply not working for me.
– user3016795
                Nov 26, 2013 at 6:05
                Found the issue for Person - Need the Person ID and not the name. When putting the ID instead of the name - it worked for that.
– user3016795
                Nov 26, 2013 at 6:13
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.