1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.