Import Sharepoint 2010 list data from Excel table using VBA

Question

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!

Solution

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.

Comments

[user3016795]

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?

[ErinsMatthew]

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.

[user3016795]

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]

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.