[Excel 2016] Query Silent Refresh?

[MrKowz]

Good day, everyone!

I'm in an environment that is running two different versions of Excel. Excel 2016 and O365. I developed a utility which has three data connections to other Excel files, and these data connections are causing me issues.

In Excel 2016, I've recently discovered that when a data connection is refreshed, the worksheet holding that data is activated. In O365 (with no code changes), these data connections silently refresh (i.e. the tabs are never activated).

Current code:

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
DoEvents
Sheets("Welcome").Activate
End Sub

Expected/Desired Outcome: The worksheet "Welcome" is activated after all connections are refreshed.

Current/Undesired Outcome: Each worksheet that has a connection is being "Activated" after code execution finishes.

Anyone have a workaround? Thanks much in advance!

 

[RoryA]

Have you tried the Calculateuntilasyncqueriesdone method?

 

[MrKowz]

Good day, Rory!

I had not tried that method, but it seems that it has made no difference when applied in the below manner.

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Sheets("Welcome").Activate
End Sub

 

[MrKowz]

Bump - Any thoughts?

 

[MrKowz]

It seems that there's something majorly broken in the specific version of Excel 2016 that my environment is running, as even using a class module to capture a Query_AfterRefresh() event didn't work. The event would properly fire, but after all VBA code execution finished, Excel would proceed to select all of the data it just refreshed.

Here is the code I attempted - this works in O365, but not the 2016 version we're running. (Ignore the messy code). I would run clsQuery_Init to build the Collection and then run FullRefresh to test.

Class Module: clsQuery

Option Explicit

Public WithEvents qQuery As QueryTable

Private Sub qQuery_AfterRefresh(ByVal Success As Boolean)
ThisWorkbook.Sheets("Welcome").Activate
End Sub

Standard Module

Option Explicit

Dim colQueries As New Collection

Private Sub clsQuery_Init()

Dim clsQ    As clsQuery
Dim qtQ     As QueryTable

Set qtQ = Sheets("TimeData").Range("TimeData")(1, 1).ListObject.QueryTable
    Set clsQ = New clsQuery
    Set clsQ.qQuery = qtQ
    colQueries.Add clsQ
Set qtQ = Sheets("ProjectList").Range("AE_Project_List")(1, 1).ListObject.QueryTable
    Set clsQ = New clsQuery
    Set clsQ.qQuery = qtQ
    colQueries.Add clsQ
Set qtQ = Sheets("Roster").Range("AE_Roster")(1, 1).ListObject.QueryTable
    Set clsQ = New clsQuery
    Set clsQ.qQuery = qtQ
    colQueries.Add clsQ

End Sub

Sub FullRefresh()
ThisWorkbook.RefreshAll
DoEvents
Application.CalculateUntilAsyncQueriesDone
End Sub

The only workaround I found is a very simple, and not-so-great one. I was able to use Application.OnTime to just fire another macro a couple of seconds after code execution finished.... no need for a class module or anything crazy!

Workaround:

No Class Module

Standard Module

Option Explicit

Public Sub FullRefresh()
ThisWorkbook.RefreshAll
DoEvents
Application.CalculateUntilAsyncQueriesDone
Application.OnTime Now + TimeValue("00:00:02"), "SplashScreen"
End Sub

Public Sub SplashScreen()
ThisWorkbook.Sheets("Welcome").Activate
End Sub