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