Solved: How to know when Webquery has finished updating

[LucasLondon]

Hello,

I have some webqueries in excel which between running via a macro and and updating of actual data on the screeen takes a few seconds. Is there a way to find out when the process of updating is complete?

The follwing is the macro to refresh the webqueries:

[VBA]
Sub webquery()

ActiveWorkbook.RefreshAll
Application.Calculate
MsgBox done
End Sub
[/VBA]
However the msgbox shows before the data is actually refreshed. I only want to show the msgbox after the data has completed refreshing, not in the middle of it while it's still doing it.

Hoep someone has a suggestion.

Thanks,

Lucas

[LucasLondon]

Did Anyone have any thoughts on this?

I did some searching on the net and came up with the code below, but the message box is shown before the actual values of the query refresh. But I need it to work the otherway round - i.e update the numbers first and then show the message box.

Hope someone can help.

Thanks,

Lucas

[VBA]
Sub Test()
Dim WAIT As Double
WAIT = Timer
While Timer < WAIT + 15
DoEvents 'do nothing

ActiveWorkbook.RefreshAll
Wend
MsgBox "done"
End Sub
[/VBA]

[ZVI]

Hi Lucas,

Copy the code into ThisWorkbook module and try it:

' Code of ThisWorkbook (class) module
Public WithEvents QT As QueryTable

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
' Place your code here if required
' ...
' Show the status
  If Success Then
    MsgBox "Query completed successfully"
  Else
    MsgBox "Query failed or was cancelled"
  End If
End Sub

Regards,
Vladimir

[mdmackillop]

Hi Lucas,
Please use the VBA tags when you post code.

[LucasLondon]

Thanks Vladimir for the suggestion,

But I'm not sure how to use this. How do I call/run the macro?

Lucas

[VBA]
Sub QT_AfterRefresh(ByVal Success As Boolean)
'Place your code here if required
' Show the status
'ActiveWorkbook.RefreshAll
If Success Then
MsgBox "Query completed successfully"
ElseMsgBox "Query failed or was cancelled"
End If
End Sub

[/VBA]

[ZVI]

How do I call/run the macro?

Being placed into ThisWorkbook VBA-module the code will be automatically triggered each time after the quire is finished.
In the attached template push the button to see where the code is.

[LucasLondon]

Thanks for the further clarrifictaion but sorry this still does not make sense.

Let me explain further what I'm trying to do with an example included in the workbook. I have added in the following macro and placed it in "this workbook"

[VBA]Sub test()
Range("A7:b50").Value = "test"
End Sub[/VBA]

Also I have included the query on the query sheet and inserted a module amd I've placed the following code to update the query:

[VBA]Sub Refreshwebqueries()
ActiveWorkbook.RefreshAll
End Sub[/VBA]

Currently I use this code to update/refresh the query and it works fine, takes a couple of seconds.

Now what I want to do is the following in order:

1) Run the Sub Refreshwebqueries() macro to refresh the query
2) AFTER the query has refreshed, automatically run the test macro

So my quetion becomes how do I do this with set up you've suggested?

So do I simply call the procedure in the query as follows by running the Sub Refreshwebqueries() macro?

[VBA]Sub QT_AfterRefresh(ByVal Success As Boolean)
'Place your code here if required
' Show the status
call test
If Success Then
MsgBox "Query completed successfully"
ElseMsgBox "Query failed or was cancelled"
End If
End Sub [/VBA]

So a) I am not sure what macro I need to call in the code above - call test as currently or call Refreshwebqueries?
b) And becuase the Sub QT_AfterRefresh sits in thisworkboook, how do I call it from the macro window?

Hope I'm making sense!

Lucas

[mdmackillop]

Give this a try

[ZVI]

Hi Lucas, thank you for calling me back from too long cycling in a business.

In addition to the short & perfect decision of mdmackillop, let me write vast explanation to compensate my absence here

In VBA-help we can find that RefreshAll "refreshes all external data ranges and PivotTable reports in the specified workbook". Therefore ActiveWorkbook.RefreshAll has sense only for such cases:
A. There is a quire to external data in the sheet
B. Pivot table is referenced to external data source

External data can be, for example, TXT or CSV text file, external XLS file, Access or SQL database, web page data as well, and so on. Excel data which are referenced to external sources can be refreshed by two ways:
1. On-line (real-time) refreshing when Excel blocks user interface during quire refreshing time.
2. Refresh in a background when you can work with Excel at quire refreshing time

Both options 1 or 2 are the parameters of the external data quire and can be set programmatically Sheet1.QueryTables(1) BackgroundQuery = True / False, or be chosen manually by right clicking on quire/pivot table and switching “refresh in background” checkbox in parameters form of the quire/pivot table.

At on-line refreshing VBA-code waits till RefreshAll has finished and only after that runs the next line of the code.

At refreshing in background VBA-code runs the next line of code after ThisWorkbook.RefreshAll immediately without waiting of refresh completing.

Refresh in a background is useful for the long or not stable refreshing time such as for the web quire when the freezing of Excel user interface is unpleasant. Fortunately Excel generates the signal of refresh finishing. It’s so called AfterRefresh quire event. To catch any event the VBA class object and WithEvents keyword are used, see more details in Events And Event Procedures In VBA. ThisWorkbook module can be used as well instead of the separate class module.
The algorithm:
1. At opening Workbook_Open in ThisWorkbook module initializes the QT object variable.
2. RefreshBackground macro starts by the aid of the button click. Macro provides some actions and call ThisWorkbook.RefreshAll in the bottom line of its code.
3. Refresh is running in a background, but Excel’s user interface is not blocking during refreshing time.
4. After refreshing the quire event AfterRefresh has happened.
5. QT_AfterRefresh subroutine in ThisWorkbook module automatically triggers AfterRefresh event and call MacroAfterRefresh
6. MacroAfterRefresh plays.

The example with both types of refreshing is attached.
Quire to the http://www.vbaexpress.com/portal.php is set in Sheet1 of the example

Full code:
Code of ThisWorkbook module:

' Code of ThisWorkbook (class) module. See also the code in Module1
Public WithEvents QT As QueryTable

' After Refresh event trigger
Private Sub QT_AfterRefresh(ByVal Success As Boolean)
  Sheet1.Range("C2") = "AfterRefresh"   ' Show the status in C2 cell
  If Success = True Then
    MsgBox "Query completed successfully"
    AfterRefreshBackground              ' Run after refresh subroutine
  Else
    MsgBox "Query failed or was cancelled"
  End If
End Sub

' Init at opening
Private Sub Workbook_Open()
  Init
End Sub

' Associate QT with QueryTable of Sheet1
Sub Init()
  On Error Resume Next
  Set QT = Sheet1.QueryTables(1)
End Sub

Code of standard Module1:

' Code of standard Module1. See also the code in ThisWorkbook module

' Button "RefreshBackground": before refresh actions with RefreshAll at the end of macro
Sub RefreshBackground()
  ' Do before refresh actions
  With Sheet1
    Thisworkbook.Init                         ' For debug only - init QT in case it was reset at debugging
    .QueryTables(1).BackgroundQuery = True    ' Refresh in a backgroung
    .Range("C2").Value = "RefreshBackground"  ' Put the status in C2 cell
    .Range("A4:H5").ClearContents             ' Clear contents of the query range
    MsgBox "RefreshBackground starts"         ' Show the status
    .Range("C2").Value = "Refreshing"         ' Put the status in C2 cell
  End With
  ' Start background refreshing
  Thisworkbook.RefreshAll
End Sub

' After refresh actions, this macro is auto called by QT_AfterRefresh code
Sub AfterRefreshBackground()
  Sheet1.Range("C2") = "MacroAfterRefresh"    ' Put the status in C2 cell
  MsgBox "MacroAfterRefresh starts"           ' Show the status
  ' Your code is here                         ' Put your after refresh code below
  ' ...                                       ' ...
  Sheet1.Range("C2").Value = "End"            ' Put End status into C2 cell
End Sub


' === On-line (real-time) refreshing code ===

' Button "RefreshOnLine"
Sub RefreshOnLine()
  Application.EnableEvents = False            ' Avoid triggering of refresh event
  With Sheet1
    ' Do before refresh actions
    .QueryTables(1).BackgroundQuery = False   ' On-line refreshing
    .Range("A4:H5").ClearContents             ' Clear contents of the query range
    .Range("C2") = "RefreshOnLine"            ' Put the status in C2 cell
    MsgBox "RefreshOnLine macro starts"       ' Show the status
    .Range("C2").Value = "Refreshing"         ' Put the status in C2 cell
    ' Resfresh
    ThisWorkbook.RefreshAll                   ' Start on-line refreshing
    ' Do after refresh actions
    AfterRefreshOnLine                        ' Call after refresh macro
    .Range("C2").Value = "End"                ' Put End status into C2 cell
  End With
  Application.EnableEvents = True             ' Restore EnableEvents
End Sub

' After refresh actions, this macro is called by RefreshOnLine macro
Sub AfterRefreshOnLine()
  Sheet1.Range("C2") = "MacroAfterRefresh"    ' Put the status in C2 cell
  MsgBox "AfterRefreshOnLine starts"          ' Show the status
  ' Your code is here                         ' Put your after refresh code below
  ' ...                                       ' ...
End Sub

Regards,
Vladimir

[LucasLondon]

Thanks MdMacKillop and ZVI for the code and explanantions.

This now makes a lot more sense. So effectively, if I didn't want to resort to using a VBA solution, I could get what I want simply by unchecking "Enable Background Refresh" under refresh control for each query that sits in the workbook?

I didn't realise it was as simple as this!

Thanks,

Lucas

[ZVI]

... if I didn't want to resort to using a VBA solution, I could get what I want simply by unchecking "Enable Background Refresh" under refresh control for each query that sits in the workbook?

Right, this can be made manually.
But to disable background refresh of each quire in all sheets of the active workbook you can instead apply macro shown below and save workbook.

Sub DisableBackgroundRefresh()
  Dim sh As Worksheet, qt As QueryTable
  For Each sh In Worksheets
    For Each qt In sh.QueryTables
      qt.BackgroundQuery = False
    Next
  Next
End Sub

Vladimir

[LucasLondon]

Great. Thanks again.

[Hamond]

Hi,

I'm having some problems updating my queries. I have unchecked Enable Background Refresh for the all the queries in the workbook (there are a maximum of three). However I find that when I run the code below it only updates one of the queries. I then have to run the same code another time to update the other query but running the code twice only works sometimes.

[vba]

Sub Refreshwebqueries()

ActiveWorkbook.RefreshAll
End Sub

[/vba]

Is the above the best code to update all the webqueries or would something else work better? E.g some kind of loop/next function within the code that tells to update each query one at a time?

Thanks,

H