I need to programmatically add a list item to a SharePoint Online list, exclusively from the VBA environment in an Excel workbook.
My current list has one column: Title (I added the below list item manually in my web browser)
Current VBA test sub below. My expectation is that this would create a new list object called "PLEASE WORK":
Sub testList()
Dim sharepointUrl As String: sharepointUrl = "https://redacted.sharepoint.com/sites/redacted/"
Dim strListGUID As String: strListGUID = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
Dim objXMLHTTP As WinHttpRequest
Set objXMLHTTP = New WinHttpRequest
'XML batch element to be inserted into SOAP body <updates>
Dim strUpdate As String
strUpdate = "<Batch OnError=""Continue"" ListVersion=""1"">"
strUpdate = strUpdate & "<Method ID=""1"" Cmd=""New"">"
'strUpdate = strUpdate & "<Field Name='ID'>New</Field>"
strUpdate = strUpdate & "<Field Name=""Title"">""PLEASE WORK""</Field>"
strUpdate = strUpdate & "2006-1-11T09:15:30Z</Field>"
strUpdate = strUpdate & "</Method>"
strUpdate = strUpdate & "</Batch>"
'SOAP body
Dim strSoapBody As String
strSoapBody = "<?xml version=""1.0"" encoding=""utf-8""?>"
strSoapBody = strSoapBody & "<soap12:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap12=""http://www.w3.org/2003/05/soap-envelope"">"
strSoapBody = strSoapBody & "<soap12:Body>"
strSoapBody = strSoapBody & "<UpdateListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
strSoapBody = strSoapBody & "<listName>" & strListGUID & "</listName>"
strSoapBody = strSoapBody & "<updates>" & strUpdate & "</updates>"
strSoapBody = strSoapBody & "</UpdateListItems>"
strSoapBody = strSoapBody & "</soap12:Body>"
strSoapBody = strSoapBody & "</soap12:Envelope>"
'Debug.Print strSoapBody
objXMLHTTP.Open "POST", sharepointUrl & "_vti_bin/Lists.asmx", False
objXMLHTTP.SetRequestHeader "Content-Type", "application/soap+xml; charset=utf-8"
objXMLHTTP.SetRequestHeader "Content-Length", Len(strSoapBody)
objXMLHTTP.Send strSoapBody
If objXMLHTTP.Status = 200 Then
'Do something with response
Debug.Print "200"
End If
Debug.Print objXMLHTTP.ResponseText 'empty response
Set objXMLHTTP = Nothing
End Sub
This code runs without error and returns an empty ResponseText, with no changes visible to the Sharepoint list. Any ideas how to debug this?
I noticed earlier that if I visit the URL [sharepointUrl & "_vti_bin/Lists.asmx"], I am given some documentation (this is for UpdateListItems)
UpdateListItems
Test
The test form is only available for requests from the local machine.
SOAP 1.1
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.
POST /_vti_bin/Lists.asmx HTTP/1.1
Host: redacted.sharepoint.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"
<?xml version="1.0" encoding="utf-8"?>
<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>string</listName>
<updates>string</updates>
</UpdateListItems>
</soap:Body>
</soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<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>
<UpdateListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<UpdateListItemsResult>string</UpdateListItemsResult>
</UpdateListItemsResponse>
</soap:Body>
</soap:Envelope>
SOAP 1.2
The following is a sample SOAP 1.2 request and response. The placeholders shown need to be replaced with actual values.
POST /_vti_bin/Lists.asmx HTTP/1.1
Host: redacted.sharepoint.com
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName>string</listName>
<updates>string</updates>
</UpdateListItems>
</soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<UpdateListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<UpdateListItemsResult>string</UpdateListItemsResult>
</UpdateListItemsResponse>
</soap12:Body>
</soap12:Envelope>
I am not sure how to best make use of this. I used info from the SOAP 1.2 example for the VBA.
EDIT: I commented out the line strUpdate = strUpdate & "2006-1-11T09:15:30Z</Field>" in the XML batch element. The VBA still runs without error, but now I have ResponseText coming through from the WinHttpRequest obj with an XML error message containing this:
Server was unable to process request. ---> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
