Hello,

First off, I do not know VBA, but have determined after hours of searching, it is the only solution to my problem.

Background:
I have a workbook with a large number of Power Query-created "Merge"-type Queries and Queries that pull data from flat files; some, but not all are added to the Data Model/Power Pivot.

The composition of queries may change over time.

Within a subset of the queries, I have disabled refreshing with "Refresh All" to reduce the already lengthy time it takes to refresh all queries.

There are a large number of worksheets that contain Pivots off the Data Model, whereas the others contain a subset of tables that are populated from the queries via "Load To"

I am using O365 Excel

The example code below is based on hours of research. I don't know if it's of value in achieving my objectives below, but I'm including it in the event it sparks a solution.

Objectives:
I would like an "index" worksheet created containing a dynamic table (or just listed out in cells that I can convert to a table manually) that keeps an up-to-date list of workbook "Query-" queries and their respective properties below.

The dynamic (e.g., reflect added/changed/deleted queries) table should contain the following columns with the following query/connection properties as fields:
-----------------------------------------------------
"Name", "Description", "RefreshWithRefreshAll", "InModel", "Type"

The following example is what I was able to cobble together from the interwebs which got me some of the way there, but it only allows me to see it in the VBA Immediate window, and I don't know how to get it into a table on the worksheet:

Sub ListConnections()

Dim cn As WorkbookConnection

Debug.Print "Name", "|", "Description", "|", "|", "RefreshWithRefreshAll", "|", "InModel", "|", "Type"


For Each cn In ThisWorkbook.Connections
Debug.Print cn.Name, "|", cn.Description, "|", cn.RefreshWithRefreshAll, "|", cn.InModel, "|", cn.Type

Next
End Sub-----------------------------------------------------

Currently, I can only get it to output in the Immediate Window:
-----------------------------------------------------
Name|Description||RefreshWithRefreshAll|InModel|Type
ModelConnection_ExternalData_1|DataModel|True|True|7
ModelConnection_ExternalData_11|DataModel|True|True|7
Query-AllADMembers(CX)|Connectiontothe'AllADMembers(CX)'queryintheworkbook.|True|True|1
Query-AllAzureADUsers-Disabled|Connectiontothe'AllAzureADUsers-Disabled'queryintheworkbook.|True|False|1
Query-AllAzureADUsers-Enabled|Connectiontothe'AllAzureADUsers-Enabled'queryintheworkbook.|True|False|1
Query-AllAzureADUsersOnly|Connectiontothe'AllAzureADUsersOnly'queryintheworkbook.|True|False|1
...

Query-UsersinDA,NotinApp-BulkDEL*|Connectiontothe'UsersinDA,NotinApp-BulkDEL*'queryintheworkbook.|False|True|1
ThisWorkbookDataModel|DataModel|True|True|7
-----------------------------------------------------
Note 1: I used the convention of adding an asterisk to the query name to visually see which queries I disabled refreshing with Refresh All.
Note 2: Query names with (CNX) refer to the base queries to the flat files upon which the other merge queries are based.
Note 3: The "Last Refresh" field in the table would be the Date/Time of the Last Refresh of the query. I was hoping it could be obtained as a property of the query, since this timestamp is shown when hovering over the query in the query pane, e.g.

<br style="letter-spacing: 0.2px;">
Based on my searches, it does not appear that this is obtainable for some reason, but if not, I found other references to adding it as a calculated value using timestamps of macro executed refreshes.

Example: I found many references to adding a button to have a macro refresh one or refresh all queries. My thought is that one could create code to dynamically enumerate the names from the code above Query-[Query Name], then have the code execute a refresh of each (CNX) query above first, then execute the rest individually to capture the execution and/or finish timestamp. Since there is a natural sequence that Excel uses when doing a refresh all, executing queries manually may trigger the natural sequence multiple times making the overall refresh times unbearable. Looking for the most efficient solution here.

Here is some code that I thought would be useful to tackle this, but again, it only shows output in the Immediate Window when executed.
-----------------------------------------------------
Dim TStart As Date
Dim TEnd As Date
Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections

If Left(cn, 13) = "Power Query -" Then
Debug.Print cn
TStart = Now
cn.Refresh
TEnd = Now
Debug.Print CStr(DateDiff("s", TStart, TEnd)) + " Seconds"
Debug.Print ""

End If
Next cn
End Sub
-----------------------------------------------------
Thanks in advance!

M