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