Listi all Queries and Connections with Last Refresh Time and Properties in Worksheet Table

[maxxis]

Hello,

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

Background:

1. 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.
2. The composition of queries may change over time.
3. 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.
4. 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"
5. I am using O365 Excel
6. 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-AnchorSearch(CNX)|Connectiontothe'AnchorSearch(CNX)'queryintheworkbook.|True|True|1
Query-VPFullLicinAppADGroups|Connectiontothe'VPFullLicinAppADGroups'queryintheworkbook.|True|True|1
Query-VPw/Licenses|Connectiontothe'VPw/Licenses'queryintheworkbook.|True|True|1
Query-VPw/oForFRLicenses|Connectiontothe'VPw/oForFRLicenses'queryintheworkbook.|True|True|1
Query-VP_Org(CNX)|Connectiontothe'VP_Org(CNX)'queryintheworkbook.|True|True|1
Query-AuditLogs(CNX)*|Connectiontothe'AuditLogs(CNX)*'queryintheworkbook.|False|False|1
Query-AuditLogs-SCIM(CNX)*|Connectiontothe'AuditLogs-SCIM(CNX)*'queryintheworkbook.|False|True|1
Query-AuditLogs-Users(CNX)*|Connectiontothe'AuditLogs-Users(CNX)*'queryintheworkbook.|False|True|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.
  
  
  • 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

 

[John_w]

Maybe this macro will get you started. It adds a new sheet, "Conns", to either the macro workbook or the active workbook and lists some properties of the workbook connections, including name and refresh time, in a table.

Add your column headings and properties to the code as required (e.g. RefreshWithRefreshAll, InModel, etc.)

The macro lists OLEDB (Type = 1) and ODBC (Type = 2) connections.

Public Sub List_Workbook_Connections()

    Dim wb As Workbook
    Dim qcSheet As Worksheet, r As Long, tableStartRow As Long
    Dim wbConn As WorkbookConnection
    Dim wbcTable As ListObject
   
    'Either operate on this macro workbook
    'Set wb = ThisWorkbook
    'Or operate on the active workbook
    Set wb = ActiveWorkbook
   
    Set qcSheet = GetWbSheet(wb, "Conns")
    If qcSheet Is Nothing Then
        With wb
            Set qcSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            qcSheet.Name = "Conns"
        End With
    End If
    qcSheet.Cells.Clear

    r = 1
    tableStartRow = r
    qcSheet.Cells(r, "A").Resize(, 5).Value = Array("Name", "Type", "RefreshDate", "Connection", "CommandText")
    r = r + 1
   
    For Each wbConn In wb.Connections
        qcSheet.Cells(r, "A").Value = wbConn.Name
        Select Case wbConn.Type
            Case Is = xlConnectionTypeODBC
                qcSheet.Cells(r, "B").Value = "ODBC"
                On Error Resume Next
                qcSheet.Cells(r, "C").Value = wbConn.ODBCConnection.RefreshDate
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.ODBCConnection.Connection
                qcSheet.Cells(r, "E").Value = wbConn.ODBCConnection.CommandText
            Case Is = xlConnectionTypeOLEDB
                qcSheet.Cells(r, "B").Value = "OLEDB"
                On Error Resume Next
                qcSheet.Cells(r, "C").Value = wbConn.OLEDBConnection.RefreshDate
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.OLEDBConnection.Connection
                qcSheet.Cells(r, "E").Value = wbConn.OLEDBConnection.CommandText
        End Select
        r = r + 1
    Next

    With qcSheet
        Set wbcTable = .ListObjects.Add(xlSrcRange, .Cells(tableStartRow, "A").Resize(r - tableStartRow, 5), , xlYes)
        wbcTable.Name = "WorkbookConnections_Table"
    End With

End Sub

 

[Alex Blakenburg]

Maybe this macro will get you started. It adds a new sheet, "Conns", to either the macro workbook or the active workbook and lists some properties of the workbook connections, including name and refresh time, in a table.

@John_w, the macro calls a "GetWbSheet" which has not been included.

 

[John_w]

Thanks for checking Alex. Here's the missing function:

Private Function GetWbSheet(wb As Workbook, sheetName As String) As Worksheet
    Set GetWbSheet = Nothing
    On Error Resume Next
    Set GetWbSheet = wb.Worksheets(sheetName)
    On Error Resume Next
End Function

 

[maxxis]

Thanks to @Alex Blakenburg, you beat me to the punch

@John_w, I am so thankful for your help!! I modified the code per your instructions to add the additional info I described above:

Option Explicit
Private Function GetWbSheet(wb As Workbook, sheetName As String) As Worksheet
    Set GetWbSheet = Nothing
    On Error Resume Next
    Set GetWbSheet = wb.Worksheets(sheetName)
    On Error Resume Next
End Function


Public Sub List_Workbook_Connections()

    Dim wb As Workbook
    Dim qcSheet As Worksheet, r As Long, tableStartRow As Long
    Dim wbConn As WorkbookConnection
    Dim wbcTable As ListObject
       
    'Either operate on this macro workbook
    'Set wb = ThisWorkbook
    'Or operate on the active workbook
    Set wb = ThisWorkbook
   
    Set qcSheet = GetWbSheet(wb, "Conns")
    If qcSheet Is Nothing Then
        With wb
            Set qcSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            qcSheet.Name = "Conns"
        End With
    End If
    qcSheet.Cells.Clear

    r = 1
    tableStartRow = r
    qcSheet.Cells(r, "A").Resize(, 9).Value = Array("Name", "Description", "RefreshDate", "RefreshWithAll", "EnableRefresh", "InModel", "Type", "ODBC/OLEDB", "CommandText")
    r = r + 1
   
    For Each wbConn In wb.Connections
        qcSheet.Cells(r, "A").Value = wbConn.Name
        Select Case wbConn.Type
            Case Is = xlConnectionTypeODBC
                On Error Resume Next
                qcSheet.Cells(r, "B").Value = wbConn.Description
                qcSheet.Cells(r, "C").Value = wbConn.ODBCConnection.RefreshDate
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.RefreshWithRefreshAll
                qcSheet.Cells(r, "E").Value = wbConn.ODBCConnection.EnableRefresh
                qcSheet.Cells(r, "F").Value = wbConn.InModel
                qcSheet.Cells(r, "G").Value = wbConn.Type
                qcSheet.Cells(r, "H").Value = "ODBC"
                qcSheet.Cells(r, "I").Value = wbConn.ODBCConnection.CommandText
                
            Case Is = xlConnectionTypeOLEDB
                On Error Resume Next
                qcSheet.Cells(r, "B").Value = wbConn.Description
                qcSheet.Cells(r, "C").Value = wbConn.OLEDBConnection.RefreshDate
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.RefreshWithRefreshAll
                qcSheet.Cells(r, "E").Value = wbConn.OLEDBConnection.EnableRefresh
                qcSheet.Cells(r, "F").Value = wbConn.InModel
                qcSheet.Cells(r, "G").Value = wbConn.Type
                qcSheet.Cells(r, "H").Value = "OLEDB"
                qcSheet.Cells(r, "I").Value = wbConn.OLEDBConnection.CommandText
        End Select
        r = r + 1
    Next

    With qcSheet
        Set wbcTable = .ListObjects.Add(xlSrcRange, .Cells(tableStartRow, "A").Resize(r - tableStartRow, 10), , xlYes)
        wbcTable.Name = "WorkbookConnections_Table"
    End With

End Sub

The resulting table pulls back everything perfectly except the most import piece of data and another that works in the immediate window, but not your code:

1. The RefreshDate is returning an empty or null value, even though a value is shown in the screenshot of the properties page for the queries.
2. The Type value is null for the Data Model rows. It works in the immediate window example I provided above, but your code does not (this is not a major problem, but am pointing it out in case there is an easy fix to make solution work as expected).

Any ideas?

Again, TIA!
M

 

[John_w]

As stated, the code only outputs ODBC and OLEDB connections. Add a Case Is = xlConnectionTypeMODEL clause and supporting code for Data Model connections.

RefreshDate was only property I found in the Object Browser which looks like it should return the UI's refresh date/time, however in my tests I got "Run-time error '1004': Application'defined or object-defined error" on the line qcSheet.Cells(r, "C").Value = wbConn.OLEDBConnection.RefreshDate, hence the preceding On Error Resume Next line to ignore this error, leaving the cell blank. The error means RefreshDate is null (not defined). You could try removing the OERN lines to see if you don't get this error, however it seems RefreshDate is not populated/updated. NB your OERN lines should precede the RefreshDate lines, not the Description lines.

 

[mlfnelson]

This is what I have been searching for!! Thank you for this!

1. The RefreshDate is returning an empty or null value, even though a value is shown in the screenshot of the properties page for the queries.

To help: I noticed that the connections that had no Refresh Date were also connections that did not have any "Locations where the connection is used in the workbook" (pivot tables tied to them).

I took the code above, removed the "RefreshDate", and found that, those connections which did not have any pivot tables tied to them, also returned a FALSE result for "EnableRefresh".

So, I modified the code to add a conditional statement for each case:

Public Sub List_Workbook_Connections()

    Dim wb As Workbook
    Dim qcSheet As Worksheet, r As Long, tableStartRow As Long
    Dim wbConn As WorkbookConnection
    Dim wbcTable As ListObject
    Dim strRefreshODBC As String 'added
    Dim strRefreshOLEDB As String 'added
       
    'Either operate on this macro workbook
    'Set wb = ThisWorkbook
    'Or operate on the active workbook
    Set wb = ActiveWorkbook
   
    Set qcSheet = GetWbSheet(wb, "Conns")
    If qcSheet Is Nothing Then
        With wb
            Set qcSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            qcSheet.Name = "Conns"
        End With
    End If
    qcSheet.Cells.Clear

    r = 1
    tableStartRow = r
    qcSheet.Cells(r, "A").Resize(, 9).Value = Array("Name", "Description", "RefreshDate", "RefreshWithAll", "EnableRefresh", "InModel", "Type", "ODBC/OLEDB", "CommandText")
    r = r + 1
   
    For Each wbConn In wb.Connections
        qcSheet.Cells(r, "A").Value = wbConn.Name
        Select Case wbConn.Type
            Case Is = xlConnectionTypeODBC
                    'start add
                    If wbConn.ODBCConnection.EnableRefresh = True Then
                        strRefreshODBC = wbConn.ODBCConnection.RefreshDate
                    Else
                        strRefreshODBC = "N/A"
                    End If
                    'end add
                
                On Error Resume Next
                qcSheet.Cells(r, "B").Value = wbConn.Description
                qcSheet.Cells(r, "C").Value = strRefreshODBC 'modified
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.RefreshWithRefreshAll
                qcSheet.Cells(r, "E").Value = wbConn.ODBCConnection.EnableRefresh
                qcSheet.Cells(r, "F").Value = wbConn.InModel
                qcSheet.Cells(r, "G").Value = wbConn.Type
                qcSheet.Cells(r, "H").Value = "ODBC"
                qcSheet.Cells(r, "I").Value = wbConn.ODBCConnection.CommandText
                
            Case Is = xlConnectionTypeOLEDB
                    'start add
                    If wbConn.OLEDBConnection.EnableRefresh = True Then
                        strRefreshOLEDB = wbConn.OLEDBConnection.RefreshDate
                    Else
                        strRefreshOLEDB = "N/A"
                    End If
                    'end add
                
                On Error Resume Next
                qcSheet.Cells(r, "B").Value = wbConn.Description
                qcSheet.Cells(r, "C").Value = strRefreshOLEDB 'modified
                On Error GoTo 0
                qcSheet.Cells(r, "D").Value = wbConn.RefreshWithRefreshAll
                qcSheet.Cells(r, "E").Value = wbConn.OLEDBConnection.EnableRefresh
                qcSheet.Cells(r, "F").Value = wbConn.InModel
                qcSheet.Cells(r, "G").Value = wbConn.Type
                qcSheet.Cells(r, "H").Value = "OLEDB"
                qcSheet.Cells(r, "I").Value = wbConn.OLEDBConnection.CommandText
        End Select
        r = r + 1
    Next

    With qcSheet
        Set wbcTable = .ListObjects.Add(xlSrcRange, .Cells(tableStartRow, "A").Resize(r - tableStartRow, 9), , xlYes)
        wbcTable.Name = "WorkbookConnections_Table"
    End With

End Sub

This worked for me, for the purposes I was looking for. I hope this works for others looking for this.