1

I have a data connection in a workbook and I refresh it using VBA. 

sub RefreshData()    
  ActiveWorkbook.Connections("LoadData1").Refresh
End Sub

The code runs without error but the data does not change or update. But when I step through the code, it works fine and the data is updated. I also tried doing a wait.

sub RefreshData()    
      ActiveWorkbook.Connections("LoadData1").Refresh
      Application.Wait (Now + TimeValue("00:00:20"))
End Sub

I have also tried ActiveSheet.Unprotect, Application.ScreenUpdating = True but to no avail.

Naresh
  • 2,984
  • 2
  • 9
  • 15
Suraj Nagabhushana Ponnaganti
  • 1,189
  • 2
  • 15
  • 30

2 Answers2

2

Simple Solution

I had issues with some of my subs ending before the data had finished refreshing. If I understand correctly, that's what's happening with your sub. If so, you could try adding the line:

Application.CalculateUntilAsyncQueriesDone

Adding this line into my code directly after the refresh command worked for me.

Note: When I had the queries set as background queries, I occasionally got weird freeze/crash issues; so I would recommend turning off the background query option with any query you use the above code with.

Complex Solution

If the simple solution doesn't work, an alternative is to add a custom class that raises an event when the refresh is finished. The downside to this solution is that you may need to rewrite existing code to be triggered by an event, instead of having an in-line refresh command.

An example of such a custom class is below. Please note that there are some assumptions built into the code -- the most prominent being that the query is set to load onto a sheet in the workbook, and refresh in the background.

To use the custom class, insert a "class module" (this is not the same as a "Module"), and copy the code from the "class code" section below into the "class module". Next, in the code module for the worksheet holding the resulting query table, add this code:

Private WithEvents queryData As QueryClass

Public Sub querySetup()
    Set queryData = New QueryClass
    Set queryData.QryTble = Me.ListObjects("QueryName").QueryTable
End Sub

Private Sub queryData_Refreshed(ByVal RefreshSuccess As Boolean, ByVal isEmpty As Boolean)

End Sub

(Note that this code is assuming that the class module has been renamed to "QueryClass", and that the query was named "QueryName". If you used different names, you'll need to adjust the code accordingly.)

You can put custom code in the queryData_Refreshed sub to happen after the query has finished refreshing. Note that the sub has two indicators -- if the query refreshed successfully, and if the query is empty (did not return any records). Then, to refresh the data, just call:

queryData.Refresh 5 'optional maximum of attempts; defualt is 1

These questions may also be helpful.

Class Code

Option Explicit

'class basics from Paul Renton, https://stackoverflow.com/questions/18136069/excel-vba-querytable-afterrefresh-function-not-being-called-after-refresh-comp

Private WithEvents mQryTble As Excel.QueryTable
Private RefreshFinished As Boolean
Private RefreshSuccessful As Boolean
Private attemptCount As Long
Private attemptMax As Long
Public Event Refreshed(ByVal RefreshSuccess As Boolean, ByVal isEmpty As Boolean)

Public Property Set QryTble(ByVal QryTable As QueryTable)
    Set mQryTble = QryTable
End Property

Public Property Get QryTble() As QueryTable
    Set QryTble = mQryTble
End Property

Public Property Get RefreshDone() As Boolean
    RefreshDone = RefreshFinished
End Property

Public Property Get RefreshSuccess() As Boolean
    RefreshSuccess = RefreshSuccessful
End Property

Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    attemptCount = attemptCount + 1
    If Success Or attemptCount = attemptMax Then
        RefreshFinished = True
        RefreshSuccessful = Success
        RaiseEvent Refreshed(Success, mQryTble.ListObject.DataBodyRange Is Nothing)
    Else
        mQryTble.ListObject.Refresh
    End If
End Sub

Public Sub Refresh(Optional attempts As Long = 1)
    If Not mQryTble.Refreshing Then
        RefreshFinished = False
        attemptMax = attempts
        mQryTble.ListObject.Refresh
    End If
End Sub
Mistella
  • 1,718
  • 2
  • 11
  • 20
  • I am experiencing the crash problem. I am connected to a data model on the Power BI Service. Unfortunately the Background refresh property is disabled for that connection. Excel crashes each time I need to refresh. Is there any other reliable workaround? – Ejaz Ahmed Jun 30 '20 at 06:42
  • 1
    @EjazAhmed I expanded my answer with an alternative option. Let me know how that works for you. – Mistella Jun 30 '20 at 15:05
  • Unfortunately I only have cube formulas in my spreadsheet. No queries. – Ejaz Ahmed Jul 01 '20 at 16:32
  • Perhaps instead of "refreshing queries", you need to "recalculate". Have you tried using [`Application.Calculate`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculate) or [`Application.CalculateFull`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculatefull) ? If your VBA code doesn't wait for that to finish (I don't remember if VBA waits for either of the `Application.Calculate` calls), [this question](https://stackoverflow.com/q/46908493/9259306) has a couple of answers that may be helpful. – Mistella Jul 02 '20 at 12:10
-1

In place of Application.Wait use-

ScheduledTime = Now + TimeValue("0:00:15")
Application.OnTime ScheduledTime, "procedure next"

optimize TimeValue based on quantum of data/connections, etc

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
Sanjay
  • 1