How to check whether Connection Refresh was successful

Question

In Excel 2016 VBA, I'm refreshing several queries like this:

MyWorkbook.Connections(MyConnectionName).Refresh

After the code is done, and no errors are encountered, I see that the hourglass icons for most of the queries are still spinning for several seconds.

Is it possible to check for success AFTER all the refreshes are completed? I'm concerned that my code isn't going to know if an error happens after the code finishes but before the queries are done refreshing.

BTW I don't want to do a RefreshAll, because some of the queries are dependent on others (uses them as a source). I refresh them in a certain sequence so that dependent queries are refreshed after the queries they are dependent on.

UPDATE:

I see that the Connection objects have a read-only RefreshDate property, which at first glance looked like it could be used to do this check:

MyWorkbook.Connections(MyConnectionName).OLEDBConnection.RefreshDate

HOWEVER, it doesn't seem to be getting set. I get an error trying to check it. If I set a Variant variable to that RefreshDate property, the variable shows as "Empty". The source is a SQL server database.

Answer 1

The QueryTable object exposes two events: BeforeRefresh and AfterRefresh.

You need to change your paradigm from procedural/imperative to event-driven.

Say you have this code in ThisWorkbook (won't work in a standard procedural code module, because WithEvents can only be in a class):

Option Explicit
Private WithEvents table As Excel.QueryTable
Private currentIndex As Long
Private tables As Variant

Private Sub table_AfterRefresh(ByVal Success As Boolean)
    Debug.Print table.WorkbookConnection.Name & " refreshed. (success: " & Success & ")"
    currentIndex = currentIndex + 1
    If Success And currentIndex <= UBound(tables) Then
        Set table = tables(currentIndex)
        table.Refresh
    End If
End Sub

Public Sub Test()
    tables = Array(Sheet1.ListObjects(1).QueryTable, Sheet2.ListObjects(1).QueryTable)
    currentIndex = 0
    Set table = tables(currentIndex)
    table.Refresh
End Sub

The tables variable contains an array of QueryTable objects, ordered in the order you wish to refresh them; the currentIndex variable points to the index in that array, for the QueryTable you want to act upon.

So when Test runs, we initialize the tables array with the QueryTable objects we want to refresh, in the order we want to refresh them.

The implicit, event-driven loop begins when table.Refresh is called and the QueryTable fires its AfterRefresh event: then we report success, and update the event-provider table object reference with the next QueryTable in the array (only if the refresh was successful), and call its Refresh method, which will fire AfterRefresh again, until the entire array has been traversed or one of them failed to update.

Answer 2

Just found this solution at Execute code after a data connection is refreshed

The bottom line is: Excel refreshes data connection in the background and thus the rest of the code is executed without interruption.

Solution: set BackgroundQuery property to False

Example:

For Each cnct In ThisWorkbook.Connections
   cnct.ODBCConnection.BackgroundQuery = False
Next cnct

Possible problem: don't know which connection it is...

Remedy: case... when...

Dim cnct as WorkbookConnection ' if option explicit
' ODBC and OLE DB
For Each cnct In ThisWorkbook.Connections
   Select case cnct.type
      case xlconnectiontypeodbc
   cnct.ODBCConnection.BackgroundQuery = False
      case xlconnectiontypeoledb
    cnct.OledbConnection.BackgroundQuery = False
   end select
Next cnct

As you can see, code above only deals with ODBC and OLE DB. Depending on what types of data connection you are using, you can expand the select case clause. Unless changed, once run, connection's BackgroundQuery will remain off.

Answer 3

Although not a precise solution, I identified a quirky work-around to display the error messages that Power Query would generally display, even when triggering a refresh from VBA. The caveat is that it will refresh all queries, but I propose a method to deal with that later on.

We all know that when you call the Workbook.RefreshAll method from VBA, for some reason, when using it with Application.CalculateUntilAsyncQueriesDone, all the Power Query Error messages get suppressed.

However, when you click on the 'Refresh All' button from the 'Data' tab of the Excel Ribbon, the error messages are displayed. Fortunately, you can trigger a ribbon command call from VBA using the CommandBars.ExecuteMso method. So I tried to call the button, and it worked - the error messages were displayed.

You need the idMso property of the 'Refresh All' button to trigger the command. It is "RefreshAll" - no surprises there.

Here is a link to an post that helps you get the idMso property for popular commands.

Calling something like this did the trick for me:

Application.CommandBars("Queries and Connections").Visible = True
ThisWorkbook.Activate
Application.CommandBars.ExecuteMso "RefreshAll"
Application.CalculateUntilAsyncQueriesDone

Note that I am opening the 'Queries and Connections' pane just so the user is aware of the status as it is happening. This is optional. And I do not set Application.ScreenUpdating = False since that freezes the pane also.

I have noticed that in some Excel Versions, you might have to set all the query connections' BackgroundQuery property to True for the Application.CalculateUntilAsyncQueriesDone method to work properly.

Now the error messages that Power Query Natively displays, get displayed when errors occur.

I'd also like to address the one shortcoming of this approach with a final tip. If you do not want to refresh all the queries, you can write some code to alter the WorkbookConnection.RefreshWithRefreshAll property of the connections you do not want to execute, to stop it from executing when the 'Refresh All' button is clicked. Here is a link to the documentation of that property.

The advantage of this approach is that, when refreshing all queries, Power Query deals with the Query Dependencies while using a shared cache - so it is better/faster than triggering individual query refreshes with code.