README SharePoint Sites #
Overview #
Lists SharePoint site collections accessible to the signed-in user, then retrieves subsites for each site collection. Authentication is via OAuth2 with MFA support using VBAuth.dll. Uses two sequential Graph API calls (Pattern A): first GET /sites?search=* to enumerate site collections, then GET /sites/{id}/sites once per site collection to retrieve its subsites. Fields returned include display name, URL, Graph site ID, created date, last modified date, and description (where present).
First page only is retrieved for both calls. If either call has further pages available (@odata.nextLink present in the response), an incomplete marker is shown: at the top of the output for site collections, and inline below the relevant site’s subsites for subsite lists.
Office apps #
- Access (tested M365)
- Excel (untested M365)
Required components #
- VBAuth.dll v1.x
- modVBAuthGlobalConstants.bas (from /shared)
- modListSharePointSites.bas (this module)
Azure Entra App Registration #
Delegated permissions:
- Sites.Read.All
One app registration covers both AppIDs. Admin consent is required before users can authenticate.
VBAuth Admin Table – Required Values #
AppID=17 — List Site Collections #
| Column Name | Required Value / Example | Notes |
|---|---|---|
| AppID | 17 | Identifies this feature’s configuration record (List Site Collections) |
| ActionName | VBAuth – SharePoint Sites (List Collections) | Display name — any value acceptable |
| ClientID | (your App Client ID) | GUID from Azure App Registration Overview blade |
| TenantID | (your Tenant ID) | GUID from Azure App Registration Overview blade |
| ObjectID | (your Object ID) | Set by PopulateMissingObjectIDs — shared registration with AppID=18 |
| ScopeClaims | Sites.Read.All | Comma-separated Graph scopes — shared registration with AppID=18 |
| Verb | GET | HTTP method |
| URLPart1 | https://graph.microsoft.com/v1.0/sites?search=* | Graph endpoint — search=* is required for delegated permissions |
| URLParameter1 | NO VALUE REQUIRED | Not used |
| URLPart2 | NO VALUE REQUIRED | Not used |
| URLParameter2 | NO VALUE REQUIRED | Not used |
| URLPart3 | NO VALUE REQUIRED | Not used |
| WaitForLogin | 60 | Seconds — NB MSXML2 timeout parameters are in milliseconds |
AppID=18 — List Subsites #
| Column Name | Required Value / Example | Notes |
|---|---|---|
| AppID | 18 | Identifies this feature’s configuration record (List Subsites) |
| ActionName | VBAuth – SharePoint Sites (List Subsites) | Display name — any value acceptable |
| ClientID | (your App Client ID) | GUID from Azure App Registration Overview blade — same registration as AppID=17 |
| TenantID | (your Tenant ID) | GUID from Azure App Registration Overview blade — same as AppID=17 |
| ObjectID | (your Object ID) | Set by PopulateMissingObjectIDs — same as AppID=17 |
| ScopeClaims | Sites.Read.All | Comma-separated Graph scopes — shared registration with AppID=17 |
| Verb | GET | HTTP method |
| URLPart1 | https://graph.microsoft.com/v1.0/sites/ | Base endpoint — site ID appended at runtime |
| URLParameter1 | Site ID from AppID=17 response | Composite format: hostname,siteCollectionId,webId |
| URLPart2 | /sites | Appended after URLParameter1 |
| URLParameter2 | NO VALUE REQUIRED | Not used |
| URLPart3 | NO VALUE REQUIRED | Not used |
| WaitForLogin | 60 | Seconds — NB MSXML2 timeout parameters are in milliseconds |
Module: modListSharePointSites.bas #
Public functions #
Public Function ListSharePointSites( _
ByRef strResult As String, _
ByRef strMessage As String) As Boolean
Code language: JavaScript (javascript)
Acquires a bearer token for AppID 17, GETs all site collections via search=*, then for each site calls AppID 18 to retrieve its subsites. Returns True on success. strResult receives a formatted multi-line display string. strMessage receives a summary on success or an error description on failure.
Private functions #
SafeString(objItem, strKey)— returns a string field from a parsed JSON Dictionary, wrapped inOn Error Resume Nextso absent optional fields return “” without error.FormatDateOnly(strIso)— trims an ISO 8601 datetime toyyyy-mm-dd.GetSubsites(strSiteID, blnIncomplete)— calls AppID 18 for a given site ID, parses subsite objects using JSONConverter, setsblnIncompleteif@odata.nextLinkis present.
Behaviour #
- Runs as the signed-in user (delegated permissions only).
GET /sites?search=*returns only sites the signed-in user has at least read access to — not necessarily every site in the tenant.- HTTP 200 is the success code for both calls.
- First page only is retrieved.
@odata.nextLinkpresence triggers incomplete markers in the output rather than automatic pagination. - The bearer token acquired for AppID 17 is reused for all AppID 18 calls — no re-authentication occurs mid-run.
- Authentication is interactive on first run; subsequent calls within the session reuse the cached token.
- HTTP timeouts use the standard
lngHTTPTimeoutconstant.
Notes #
- Sites.Read.All is required — there is no narrower delegated scope that supports
GET /sites?search=*. - Admin consent is required before users can authenticate.
GET /sites(withoutsearch=*) returns an empty collection for delegated permissions — thesearch=*parameter is mandatory.- The
idfield returned per site is in the composite formathostname,siteCollectionId,webId— this is the correct format to pass as{site-id}to the subsites endpoint. - Personal sites (OneDrive for Business) may appear in results if the user has access; these are identifiable by
isPersonalSite: truein the raw response (not surfaced in the display output as it is not a field developers typically need to act on differently).
VBAuth, developed and maintained by Andrew Richards (The IT Service Ltd) and Peter Bryant (Corylus Business Systems), © 2025–2026. All rights reserved. https://vbauth.com