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

maxxis

New Member
Joined
Mar 20, 2017
Messages
2
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:
VBA Code:
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:

Rich (BB code):
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.
    Image
    • 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.
VBA Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,003
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.

VBA Code:
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,758
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,003
Thanks for checking Alex. Here's the missing function:
VBA Code:
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

New Member
Joined
Mar 20, 2017
Messages
2
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:

VBA Code:
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
 

Attachments

  • 2021-06-02_0-15-01.png
    2021-06-02_0-15-01.png
    148.8 KB · Views: 10

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,003
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.
 

Forum statistics

Threads
1,141,769
Messages
5,708,420
Members
421,568
Latest member
Huxley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top