The code worked very well, but these last days just stopped to work. The .responseText is now:
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"
I tried with and without the line: objXMLHTTP.setRequestHeader "Authorization" And got the same Error.
With and without User and Password at line objXMLHTTP.Open, and still the same problem, Access Denied.
Can you guys help me please?
Sub selectSharepoint()
Dim objXMLHTTP As MSXML2.XMLHTTP
Dim strBatchXml As String
Dim strSoapBody As String
UserName = "john@email.com"
Password = "123456"
Set objXMLHTTP = New MSXML2.XMLHTTP
objXMLHTTP.Open "POST", "https://ts.sharepoint.com/sites/2018_teste/_vti_bin/lists.asmx", False, UserName, Password
'objXMLHTTP.Open "POST", "https://ts.sharepoint.com/sites/2018_teste/_vti_bin/lists.asmx", False 'This way worked the same, and got the same error.
objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=UTF-8"
objXMLHTTP.setRequestHeader "Content-Length", "length"
'objXMLHTTP.setRequestHeader "Authorization", "Basic " + Base64Encode(UserName & ":" & Password) 'makes no effect, with or without Base64Encode
objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems"
strSoapBody = "<?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>" & _
" <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" & _
" <listName>AllNames</listName>" & _
" <query>" & _
" <Query xmlns=''>" & _
" <Where>" & _
" <Eq>" & _
" <FieldRef Name='Name' />" & _
" <Value Type='Text'>Cloe</Value>" & _
" </Eq>" & _
" </Where>" & _
" <OrderBy>" & _
" <FieldRef Name='ID' />" & _
" </OrderBy>" & _
" </Query>" & _
" </query>"
strSoapBody = strSoapBody & _
" <rowLimit>1</rowLimit>" & _
" <viewFields><ViewFields>" & _
" <FieldRef xsi:type='s:string' Name='Name'></FieldRef>" & _
" </ViewFields></viewFields>" & _
" </GetListItems>" & _
" </soap:Body>" & _
" </soap:Envelope>"
objXMLHTTP.send strSoapBody
If objXMLHTTP.Status <> 200 Then
MsgBox ("Error! " & Chr(10) & objXMLHTTP.responseText)
End
Else
tudo = objXMLHTTP.responseText
End If
End Sub
MsgBox Error: MsgBox Error
User contributions licensed under CC BY-SA 3.0