I'm trying to get elements of a Sharepoint list with VBA to show and use them in an Excel sheet.
The normal get request on MSXML2.ServerXMLHTTP60
Dim xmlObj as Object
Dim sUrl as String
sUrl = "http://<SarepointUrl>/_api/web/lists/GetByTitle('<listTitle>')/items"
Set xmlObj = NEw MSXML2.ServerXMLHTTP60
xmlObj.Open "GET", sUrl, False
xmlObj.setRequestHeader "Content-Type", "text/xml; charset=UTF-8"
xmlObj.send
Debug.Print xmlObj.responseText
responds with an exception:
System.UnauthorizedAccessException Access denied. You don't have permissions to perform this action.
I tried to reach the xml with an InternetExplorer object, navigating to the url of the request:
.../getbytitle('listTitle')/items but this page on internet explorer is treated like an html page. I can take its content with InternetExplorer.Document.body.outerText and I would have a string with all the xml but I can't parse it in a real xml to extract datas.
Dim argUrl As String
Dim getUrl As String
Dim oHTMLDoc As HTMLDocument
Dim oXml As MSXML2.DOMDocument60
Dim objIE As New InternetExplorerMedium
argUrl = "https://<SharepointUrl>"
objIE.Visible = False
objIE.Silent = False
objIE.Navigate (argUrl)
objIE.Visible = False
Application.StatusBar = argUrl & " is loading. Please wait..."
Do While objIE.ReadyState = 4: DoEvents: Loop
Do Until objIE.ReadyState = 4: DoEvents: Loop
getUrl = "https://<SharepointUrl>/_api/web/lists/GetByTitle('ListTitle')/items"
objIE.Navigate (getUrl)
Application.StatusBar = getUrl & " is loading. Please wait..."
Do While objIE.ReadyState = 4: DoEvents: Loop
Do Until objIE.ReadyState = 4: DoEvents: Loop
Set oHTMLDoc = objIE.Document
Set oXml = New MSXML2.DOMDocument60
sXML = oHTMLDoc.body.outerText
Debug.Print sXML
'at this point it doesnt load the string as an XML because maybe this method (loadXML) needs an xml file instead of a string'
If oXml.LoadXML(sXML) Then
Debug.Print "IF"
Set nodeXML = xmlDoc
End If
Anyone has been able to solve all these problems to make a get request on a sharepoint list with vba?
