1

I have called SharePoint web service from excel macro VBA with following code from here:

Sub CallService()

    Call Add_Item("List1", "https://contoso.sharepoint.com/sites/site1/", "test item", "Title")

End Sub

.

Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

Dim objXMLHTTP As MSXML2.XMLHTTP60

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP60

strListNameOrGuid = ListName


'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='2' Cmd='New'><Field Name='ID'/><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 // 'returns objXMLHTTP.Status = 400 here
//' something code
End If

Set objXMLHTTP = Nothing

End Sub

But Above code returns objXMLHTTP.Status = 400

Can't find what is wrong. Can you ?

2

There is single quote ' missing in strBatchXml Name field:

Replace

<Field Name=" + FieldNameVar + "> 

To

<Field Name='" + FieldNameVar + "'>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.