excelvbaxmlsharepointsoap

Excel Macro pushing data to mulitple name column using SOAP XML


I've been writing a (surprisingly) simple VBA macro for my Excel file to add and upload data to an item on a SharePoint list. Everything works fine but I cannot, for the life of me, figure out what format/array/delimiter I need to use in order to be able to upload multiple names to a "name column" in the SharePoint list. The Name Column is set to accept multiple choice of names.

If I add a single name it works fine using UserID

soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""UserID1;#jdoe@doe.com""</Field>"

but multiple persons do no work. Here are some formats I've tried using:

soapBody = soapBody & _
  "<Field Name=""BUPS_x0020_names"">""jdoe@doe.com;bdoe@doe.com""</Field>"
soapBody = soapBody & _
  "<Field Name=""BUPS_x0020_names"">""jdoe@doe.com;#bdoe@doe.com""</Field>"
soapBody = soapBody & _
   "<Field Name=""BUPS_x0020_names"">""displaynamejoe,jdoe@doe.com;#displaynamedoe,bdoe@doe.com""</Field>"
soapBody = soapBody & _
   "<Field Name=""BUPS_x0020_names"">UserID1;#UserID2""</Field>"

EDIT: Here is an example of the full code:

Sub AddItemToSharePointList()
    Dim siteURL As String
    Dim listName As String
    Dim xmlhttp As Object
    Dim soapBody As String
    Dim Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, BUPSnames As String
    
    With ActiveWorkbook.Sheets("Requestor")
            Value1 = .Cells(3, 1).Value
            Value2 = .Cells(3, 2).Value
            Value3 = .Cells(4, 3).Value
            Value4 = .Cells(3, 4).Value
            Value5 = .Cells(3, 5).Value
            Value6 = .Cells(3, 9).Value
            Value7 = .Cells(3, 10).Value
            Value8 = .Cells(3, 11).Value
            'BUPSnames = .Cells(5, 12)

    End With

    ' Set the URL of the SharePoint site and the name of the list
    siteURL = "https://skydrivexx.sharepoint.com/sites/Testsite"
    listName = "My Test List"

    ' Create XMLHTTP object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")

    ' Create the SOAP body
    soapBody = "<?xml version=""1.0"" encoding=""utf-8""?>"
    soapBody = soapBody & "<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/"">"
    soapBody = soapBody & "<soap:Body>"
    soapBody = soapBody & "<UpdateListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
    soapBody = soapBody & "<listName>" & listName & "</listName>"
    soapBody = soapBody & "<updates>"
    soapBody = soapBody & "<Batch OnError=""Continue"" ListVersion=""1"">"
    
    
    soapBody = soapBody & "<Method ID=""1"" Cmd=""New"">"
   
    soapBody = soapBody & "<Field Name=""Value1_x0020_Col"> & Value1 & </Field>"
    soapBody = soapBody & "<Field Name=""Value2_x0020_Col""> & Value2 & </Field>"
    soapBody = soapBody & "<Field Name=""Value3_x0020_Col"">" & Value3 & " </Field>"
    soapBody = soapBody & "<Field Name=""Value4_x0020_Col"">" & Value4 & "</Field>"
    soapBody = soapBody & "<Field Name=""Value5_x0020_Col"">" & Value5 & "</Field>"
    soapBody = soapBody & "<Field Name=""Value6_x0020_Col"">" & Value6 & "</Field>"
    soapBody = soapBody & "<Field Name=""Value7_x0020_Col"">" & Value7 & "</Field>"
    soapBody = soapBody & "<Field Name=""Value8_x0020_Col"">" & Value8 & "</Field>"
    
    'For column with mulitple names I have Tried:
    'The SharePoint UserID seems to be the accepted format and works with a single user:
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">UserID</Field>"
    'Trying below format results in only the last user is added to the column (UserID2;#UserID2;#bdoe@doe.com)  
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""UserID1;#jdoe@doe.com;#UserID2;#bdoe@doe.com""</Field>"
    
    'These doesn't work:
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""<ArrayOfUser><User>UserID1</User><User>UserID2</User></ArrayOfUser>""</Field>"
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""<ArrayOfUser><User>UserID1;#jdoe@doe.com</User><User>UserID2;bdoe@doe.com</User></ArrayOfUser>""</Field>"
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""<ArrayOfUser><User>jdoe@doe.com</User></ArrayOfUser>""</Field>"
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""bdoe@doe.com""</Field>"
    'soapBody = soapBody & "<Field Name=""BUPS_x0020_names"">""UserID1;#UserID2""</Field>"
    
            
    soapBody = soapBody & "</Method>"
            

            
    soapBody = soapBody & "</Batch>"
    soapBody = soapBody & "</updates>"
    soapBody = soapBody & "</UpdateListItems>"
    soapBody = soapBody & "</soap:Body>"
    soapBody = soapBody & "</soap:Envelope>"

    ' Open the connection to the SharePoint SOAP service
    xmlhttp.Open "POST", siteURL & "/_vti_bin/Lists.asmx", False
    xmlhttp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    xmlhttp.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

    ' Send the request
    xmlhttp.Send soapBody

    ' Check the response status
    If xmlhttp.Status = 200 Then
        MsgBox "Item added successfully!"
    Else
        MsgBox "Error: " & xmlhttp.Status & " - " & xmlhttp.statusText & vbCrLf & xmlhttp.responseText
    End If

    ' Clean up
    Set xmlhttp = Nothing
End Sub

Solution

  • In testing using your code, with a simple list with just a "Name" and a "Person" field, this worked for me:

    soapBody = soapBody & "<Method ID=""1"" Cmd=""New"">"
    soapBody = soapBody & "<Field Name=""Name"">" & "Fred" & "</Field>"
    soapBody = soapBody & "<Field Name=""Person"">12;#jeff@contoso.com;#154;#mary@contoso.com</Field>"
    

    Both names show up in the "person" field after the record is added.