PDA

View Full Version : Word VBA SharePoint API to Populate Combobox


AndreaM
04-17-2020, 04:55 AM
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 (https://spaceagetech.sharepoint.com/sites/Demand/_api/Web/GetFolderByServerRelativeUrl('Shared%20Documents/General/Clients')?$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/_api/Web/GetFolderByServerRelativePath(decodedurl='/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/_api/Web/GetFolderByServerRelativePath(decodedurl='/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">
<m:properties>
<d:Exists m:type="Edm.Boolean">true</d:Exists>
<d:IsWOPIEnabled m:type="Edm.Boolean">false</d:IsWOPIEnabled>
<d:ItemCount m:type="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:type="Edm.DateTime">2019-10-02T10:42:28Z</d:TimeCreated>
<d:TimeLastModified m:type="Edm.DateTime">2019-10-02T10:42:42Z</d:TimeLastModified>
<d:UniqueId m:type="Edm.Guid">12c15367-70941-5c6g-82c5-010ec4a77gg3</d:UniqueId>
<d:WelcomePage/>
</m:properties>
</content>
</entry>
AndreaM
04-24-2020, 01:22 AM
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