In Access 2007, is it possible to loop though items in a custom category?

Question

In the Navigation Pane of Access 2007, we have the "Custom" category and can create a custom category, name it something, and add tables, queries, etc. Is it possible with VBA to loop though all the items in a custom category?

For example, I created a category named "Validations" and it contains 5 queries. I'd like to loop through each query and run it. In pseudocode, it would look like this:

For Each Query in Validations
    DoCmd.OpenQuery "Query"

EDIT: I started looking into it, and it looks like I need to access the MSysNavPaneGroups table to get the ID of my custom group. In my case, ID = 235

Additionally, is it possible to create an Excel spreadsheet during this process, and if the query returns >0 rows, have it paste the rows into Sheet1, Sheet2, etc? Something like:

Count = 1

For Each Query in Validations
    DoCmd.OpenQuery "Query"
    If Query.Rows > 0
        Excel.Sheet(i).Name = Query.Name
        Excel.Sheet(i).Rows = Query.Rows
        Count = Count + 1
    End If

I haven't even started to look into the Excel part myself, so it's ok if you don't want to mess with it. I'm more concerned about looping through the queries in the custom category.

As always, thank you!

EDIT: I created a query named "GetValidationNames" using Gord's SQL below:

SELECT MSysNavPaneGroups.Name AS GroupName, MSysNavPaneGroupToObjects.Name AS ObjectName
FROM MSysNavPaneGroups INNER JOIN MSysNavPaneGroupToObjects 
    ON MSysNavPaneGroups.Id = MSysNavPaneGroupToObjects.GroupID
WHERE (((MSysNavPaneGroups.GroupCategoryID)=3))
ORDER BY MSysNavPaneGroups.Name, MSysNavPaneGroupToObjects.Name;

Then created a form with a button, and in the Button_Click() I added the following:

Dim rs as Recordset
Set rs = CurrentDb.OpenRecordset("GetValidationNames")

Do While Not rs.EOF
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, rs("ObjectName"), _
        "C:\users\username\Desktop\ValidationResults.xlsx"
    rs.MoveNext
Loop

This runs all the queries, and adds each one as a new tab to the existing Excel file "ValidationResults.xlsx"

Answer 1

I just cooked this up so I haven't given it any rigorous testing, but it seems to list all of the objects assigned to custom categories

SELECT MSysNavPaneGroups.Name AS GroupName, MSysNavPaneGroupToObjects.Name AS ObjectName
FROM MSysNavPaneGroups INNER JOIN MSysNavPaneGroupToObjects 
    ON MSysNavPaneGroups.Id = MSysNavPaneGroupToObjects.GroupID
WHERE (((MSysNavPaneGroups.GroupCategoryID)=3))
ORDER BY MSysNavPaneGroups.Name, MSysNavPaneGroupToObjects.Name;

It could likely be extended to return object types if a category contained more than one type of object (e.g., tables and queries).