Word VBA SharePoint API to Populate Combobox

[AndreaM]

Hi, can anyone help with the required VBA code to get data via api then loop through the data to return just the Name field. The API string as /_api/Web/GetFolderByServerRelativeUrl('Shared%20Documents/General/NewFolder')?$expand=Folders&$FilterField=Name essentially looks for he Folder names in a SharePoint Document Library Folder and I would like to just return the d:Name field (or Folder name) into a Combobox in a Word Userform.

Sample code returned but looking to populate only the entry as d:Name as highlighted below:

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml" xml:base="https://fumble.sharepoint.com/sites/Review/_api/">
<id>
https://fumble.sharepoint.com/sites/.../Review/Shared Documents/General/NewFolder')
</id>
<category term="SP.Folder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link rel="edit" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Files" type="application/atom+xml;type=feed" title="Files" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/Files"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ListItemAllFields" type="application/atom+xml;type=entry" title="ListItemAllFields" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/ListItemAllFields"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ParentFolder" type="application/atom+xml;type=entry" title="ParentFolder" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/ParentFolder"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Properties" type="application/atom+xml;type=entry" title="Properties" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/Properties"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/StorageMetrics" type="application/atom+xml;type=entry" title="StorageMetrics" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/StorageMetrics"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Folders" type="application/atom+xml;type=feed" title="Folders" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder')/Folders">
<m:inline>
<feed>
<id>f9543195-g475-30t5-ab3j-17e5c2f38e9d</id>
<title/>
<updated>2020-04-17T10:22:52Z</updated>
<entry>
<id>
https://fumble.sharepoint.com/sites/.../Review/Shared Documents/General/NewFolder/Folder1')
</id>
<category term="SP.Folder" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link rel="edit" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Files" type="application/atom+xml;type=feed" title="Files" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/Files"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ListItemAllFields" type="application/atom+xml;type=entry" title="ListItemAllFields" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/ListItemAllFields"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ParentFolder" type="application/atom+xml;type=entry" title="ParentFolder" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/ParentFolder"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Properties" type="application/atom+xml;type=entry" title="Properties" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/Properties"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/StorageMetrics" type="application/atom+xml;type=entry" title="StorageMetrics" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/StorageMetrics"/>
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Folders" type="application/atom+xml;type=feed" title="Folders" href="Web/GetFolderByServerRelativePath(decodedurl='/sites/Review/Shared%20Documents/General/NewFolder/Folder1')/Folders"/>
<title/>
<updated>2020-04-17T10:22:52Z</updated>
<author>
<name/>
</author>
<content type="application/xml">
<mroperties>
<d:Exists m="Edm.Boolean">true</d:Exists>
<d:IsWOPIEnabled m="Edm.Boolean">false</d:IsWOPIEnabled>
<d:ItemCount m="Edm.Int32">2</d:ItemCount>
<d:Name>Folder1</d:Name>
<d:ProgID m:null="true"/>
<d:ServerRelativeUrl>
/sites/Review/Shared Documents/General/NewFolder/Folder1
</d:ServerRelativeUrl>
<d:TimeCreated m="Edm.DateTime">2019-10-02T10:42:28Z</d:TimeCreated>
<d:TimeLastModified m="Edm.DateTime">2019-10-02T10:42:42Z</d:TimeLastModified>
<d:UniqueId m="Edm.Guid">12c15367-70941-5c6g-82c5-010ec4a77gg3</d:UniqueId>
<d:WelcomePage/>
</mroperties>
</content>
</entry>

[AndreaM]

I managed to get this far by following this article -

https://medium.com/@anoopt/accessing-sharepoint-data-using-postman-sharepoint-rest-api-76b70630bcbf

to get the Access Token to use in my VBA as below. So now I have all the data in an XML however large as I am battling to filter down the information to only return what I need which is Folder Name and a metadata field called CompanyNumber. Can anyone assist in looping through the XML to only return the Folder Name and CompanyNumber (Metadata Column) that is associated to that Folder?

Public Sub UserForm_Initialize()
Dim xmlhttp As New MSXML2.XMLHTTP60, myURL As String


Client.Clear


    auth = "Bearer " & "this is unique for everyone so you will need to get your own access tokenNRS1NSWSIsImtpZCI6IkN0VHVoTUptRDVNN0RMZHpEMnYyeDNRS1NSWSJ9." _
& "this is just an example of how it will look without this text but follow the article above to get your access tokenmQ3eKViuvokdbiWyF76s11JAUCmj2bQ0q5JO1DfGh25LjeHWw"


sUrl = "https://fumble.sharepoint.com/sites/Review/_api/web/GetFolderByServerRelativeUrl('Shared%20Documents/General/NewFolder')?$expand=Folders/ListItemAllFields/FieldValuesAsText&$select=ItemCount,Folders/ItemCount,Folders/Name,Folders/ListItemAllFields/FieldValuesAsText"


    With xmlhttp
        .Open "GET", sUrl, False
        .setRequestHeader "Accept", "application/xml;odata=verbose"
        .setRequestHeader "Authorization", auth
        .send
    End With
    
While Not xmlhttp.readyState = 4
Wend


Debug.Print (xmlhttp.responseText)


    Dim oSeqNodes, oSeqNode As IXMLDOMNode
    
    Set oSeqNodes = xmlhttp.responseXML.getElementsByTagName("d:Name")
    If oSeqNodes.Length = 0 Then
       MsgBox "Could not retrieve information from SharePoint. Please ensure that you have access to the Demand Team Site."
    Else
        For Each oSeqNode In oSeqNodes
             Client.AddItem oSeqNode.Text
        Next
    End If


End Sub