Excel Odbc Data Connection Query Time Taken To Refresh Each Query

I am trying to test out three variations of a query that is ran from an Excel data connection. I have three individual data connections and three individual tabs that get the data

Solution 1:

Something like this perhaps (assumes all connections place their results in a worksheet table, not in a pivottable):

Sub TimeQueries()
    Dim oSh As Worksheet
    Dim oCn As WorkbookConnection
    Dim dTime AsDoubleForEach oCn In ThisWorkbook.Connections
        dTime = Timer
        oCn.Ranges(1).ListObject.QueryTable.Refresh False
        Debug.Print Timer - dTime, oCn.Name, oCn.Ranges(1).Address(external:=True)
    NextEndSub

To run this:

1. Alt+F11 to go to the VBA editor.
2. From menu: Insert Module.
3. Paste code in the window.
4. Close VBA editor.
5. Alt+F8 brings up list of macro's. Pick the new one and click run.
6. Alt+F11 again to the VBA editor.
7. Ctrl+G opens the immediate pane with the results.

If you want the code to write to a cell, use this version:

Sub TimeQueries()
    Dim oSh As Worksheet
    Dim oCn As WorkbookConnection
    Dim dTime AsDoubleDim lRow AsLongSet oSh = Worksheets("Sheet4") 'Change to your sheet name!
    oSh.Cells(1,1).Value = "Name of Connection"
    oSh.Cells(1,2).Value = "Location"
    oSh.Cells(1,1).Value = "Refresh time (s)"ForEach oCn In ThisWorkbook.Connections
        lRow = lRow + 1
        dTime = Timer
        oCn.Ranges(1).ListObject.QueryTable.Refresh False
        oSh.Cells(lRow,3).Value = Timer - dTime
        oSh.Cells(lRow,1).Value = oCn.Name
        oSh.Cells(lRow,2).Value = oCn.Ranges(1).Address(external:=True)
    NextEndSub