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?