README Sites and Subsites

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 NameRequired Value / ExampleNotes
AppID17Identifies this feature’s configuration record (List Site Collections)
ActionNameVBAuth – 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
ScopeClaimsSites.Read.AllComma-separated Graph scopes — shared registration with AppID=18
VerbGETHTTP method
URLPart1https://graph.microsoft.com/v1.0/sites?search=*Graph endpoint — search=* is required for delegated permissions
URLParameter1NO VALUE REQUIREDNot used
URLPart2NO VALUE REQUIREDNot used
URLParameter2NO VALUE REQUIREDNot used
URLPart3NO VALUE REQUIREDNot used
WaitForLogin60Seconds — NB MSXML2 timeout parameters are in milliseconds

AppID=18 — List Subsites #

Column NameRequired Value / ExampleNotes
AppID18Identifies this feature’s configuration record (List Subsites)
ActionNameVBAuth – 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
ScopeClaimsSites.Read.AllComma-separated Graph scopes — shared registration with AppID=17
VerbGETHTTP method
URLPart1https://graph.microsoft.com/v1.0/sites/Base endpoint — site ID appended at runtime
URLParameter1Site ID from AppID=17 responseComposite format: hostname,siteCollectionId,webId
URLPart2/sitesAppended after URLParameter1
URLParameter2NO VALUE REQUIREDNot used
URLPart3NO VALUE REQUIREDNot used
WaitForLogin60Seconds — 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 in On Error Resume Next so absent optional fields return “” without error.
  • FormatDateOnly(strIso) — trims an ISO 8601 datetime to yyyy-mm-dd.
  • GetSubsites(strSiteID, blnIncomplete) — calls AppID 18 for a given site ID, parses subsite objects using JSONConverter, sets blnIncomplete if @odata.nextLink is 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.nextLink presence 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 lngHTTPTimeout constant.

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 (without search=*) returns an empty collection for delegated permissions — the search=* parameter is mandatory.
  • The id field returned per site is in the composite format hostname,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: true in 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

Updated on April 3, 2026