Messagbe box "Refresh complete" when any Queires in current workbook have finished refreshing

gazmoz17

Board Regular
Joined
Sep 18, 2020
Messages
158
Office Version
  1. 365
Platform
  1. Windows
Hi,

Im struggling with this. Im using power query with ODBC connector. When any queries are refreshing I want a message box popup when all refreshes have finished.

Many Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I am not good at VBA by any measure, and in fact don't like using it at all! However, there are times when it's needed, and this is certainly one of those times.

There's no way I could write a VBA script to do what's needed, however ChatGPT has proven to be pretty good at VBA and a LOT better than I am!

I posed the question and at first it gave me an answer that didn't wait for Refreshes to complete, so I asked if the script it provided waited for the Refreshes to complete, and it answered no, but then provided the following code that would:
VBA Code:
Sub RefreshAllAndWait()

    ' Refresh all the data connections in the workbook
    ThisWorkbook.RefreshAll
   
    ' Wait for the RefreshAll operation to complete
    WaitForRefreshAll
   
    ' Display a message box
    MsgBox "Refresh All completed."
   
End Sub

Sub WaitForRefreshAll()
   
    Dim sheet As Worksheet
    Dim queryTable As QueryTable
   
    ' Loop through all the worksheets in the workbook
    For Each sheet In ThisWorkbook.Worksheets
       
        ' Loop through all the query tables in the worksheet
        For Each queryTable In sheet.QueryTables
           
            ' Wait for each query table to complete refreshing
            Do While queryTable.Refreshing
                DoEvents
            Loop
           
        Next queryTable
       
    Next sheet
   
    ' Force a recalculation of all the formulas in the workbook
    ThisWorkbook.ForceFullCalculation
    ThisWorkbook.Save
   
End Sub
I like the fact that the code incorporates a full recalculation of the workbook and saves it before displaying the Completed message. For someone like me that doesn't like having to use VBA, this is a real boon!

From ChatGPT: "Note that the WaitForRefreshAll subroutine only waits for query tables to complete refreshing. If you have pivot tables or charts that need to be refreshed, you may need to add additional code to wait for those operations to complete as well."

Just to show how I got to the answer, the first question I asked was "Excel VBA to display a message when Refresh All has completed.". The response to that request generated VBA that initiated RefreshAll and then sent a message but didn't wait for the Refreshes to complete. So I then requested "Will that script wait for the RefreshAll to complete?" and got the response above. The response had a very detailed explanation as well as the code needed. I only added the item about the Pivot Tables/Charts above because I've worked on this issue in the past and knew that they needed their own refresh.

NOTE: I have not tested the code at all, so use at your own risk! I would suggest making a copy of the workbook you need it for and trying it on the copy before putting it into your final workbook.

Hope that helps!
 
Last edited:
Upvote 0
Please don't just post code written by chatGPT that you haven't even tested. As is often the case, that code looks plausible but doesn't actually work. (it won't even run as written)
 
Upvote 0
Please don't just post code written by chatGPT that you haven't even tested. As is often the case, that code looks plausible but doesn't actually work. (it won't even run as written)
OK. I did review the code and have dealt with refreshing in VBA and while I can't guarantee it works, it looks good to me. Just so you know I didn't just post it without checking it at all.
 
Upvote 0
It won't even compile as written because ThisWorkbook.ForceFullCalculation is a property, not a method.
 
Upvote 1
Hi,

Im struggling with this. Im using power query with ODBC connector. When any queries are refreshing I want a message box popup when all refreshes have finished.

Many Thanks

If we are only monitoring the connections that were loaded to worksheets as a table, then perhaps we can create QueryTable objects by using a class module to be able to access each query table'sBeforeUpdate and AfterUpdate events. Then we can define a public variable to keep as the counter, increase this variable's value by one in the BeforeUpdate event procedure, and decrease it by one in the AfterUpdate event procedure. Finally, we can test this variable in the AfterUpdate method and if it is zero then it means all refreshed.

(Connections-only queries won't work with this method. I looked at it quickly, but I don't think we can access WorkbookConnection object events by using WithEvents in a class module.)

1- Create a standard module, name it as modMain, and copy and paste the following code into this new module.

VBA Code:
Public intCounter As Integer
Dim qryTables As Collection

Sub initQueryTables()
Dim sht As Worksheet
Dim lst As ListObject
Dim qryTable As clsQuery
    Set qryTables = New Collection
    For Each sht In ThisWorkbook.Worksheets
        For Each lst In sht.ListObjects
            Set qryTable = New clsQuery
            Set qryTable.QueryTable = lst.QueryTable
            qryTables.Add qryTable
        Next lst
    Next sht
End Sub

2- Create a class module, name it as clsQuery, and copy and paste the following code into this new class module.


VBA Code:
Private WithEvents m_QueryTable As QueryTable

Private Sub Class_Terminate()
    Set m_QueryTable = Nothing
End Sub

Public Property Set QueryTable(qryTable As QueryTable)
    Set m_QueryTable = qryTable
End Property

Private Sub m_QueryTable_AfterRefresh(ByVal Success As Boolean)
    modMain.intCounter = intCounter - 1
    If modMain.intCounter = 0 Then
        MsgBox "All refreshed!"
    End If
End Sub

Private Sub m_QueryTable_BeforeRefresh(Cancel As Boolean)
    modMain.intCounter = modMain.intCounter + 1
End Sub

3- We need to call the initialization macro. We can do that in the Workbook_Open even procedure of the ThisWorkbook object class module. Double-click on it in the VBA project pane, find the Workbook_Open event procedure, and make sure to call the modMain.initQueries macro as shown below:

VBA Code:
Private Sub Workbook_Open()
    modMain.initQueryTables
End Sub

4- Save the workbook as a macro-enabled workbook, close it, and reopen it. Then try refreshing all queries.
 
Upvote 2
Solution
Hi Smozgur, apologies for the late reply...I really appreciate your time with this. This code would be very useful to users of my workbook who are have no knowledge of pwer query etc so they know to await the updating of the query upon opening.

Unfortunately Im gettin an error. I just gave this sub a dff name & it no longer gave me an error however it didnt call the message box.

1677512847405.png


Is my earlier open work book procedure conflicting with yours?

Many Thanks
Gareth
 
Upvote 0
I am not good at VBA by any measure, and in fact don't like using it at all! However, there are times when it's needed, and this is certainly one of those times.

There's no way I could write a VBA script to do what's needed, however ChatGPT has proven to be pretty good at VBA and a LOT better than I am!

I posed the question and at first it gave me an answer that didn't wait for Refreshes to complete, so I asked if the script it provided waited for the Refreshes to complete, and it answered no, but then provided the following code that would:
VBA Code:
Sub RefreshAllAndWait()

    ' Refresh all the data connections in the workbook
    ThisWorkbook.RefreshAll
  
    ' Wait for the RefreshAll operation to complete
    WaitForRefreshAll
  
    ' Display a message box
    MsgBox "Refresh All completed."
  
End Sub

Sub WaitForRefreshAll()
  
    Dim sheet As Worksheet
    Dim queryTable As QueryTable
  
    ' Loop through all the worksheets in the workbook
    For Each sheet In ThisWorkbook.Worksheets
      
        ' Loop through all the query tables in the worksheet
        For Each queryTable In sheet.QueryTables
          
            ' Wait for each query table to complete refreshing
            Do While queryTable.Refreshing
                DoEvents
            Loop
          
        Next queryTable
      
    Next sheet
  
    ' Force a recalculation of all the formulas in the workbook
    ThisWorkbook.ForceFullCalculation
    ThisWorkbook.Save
  
End Sub
I like the fact that the code incorporates a full recalculation of the workbook and saves it before displaying the Completed message. For someone like me that doesn't like having to use VBA, this is a real boon!

From ChatGPT: "Note that the WaitForRefreshAll subroutine only waits for query tables to complete refreshing. If you have pivot tables or charts that need to be refreshed, you may need to add additional code to wait for those operations to complete as well."

Just to show how I got to the answer, the first question I asked was "Excel VBA to display a message when Refresh All has completed.". The response to that request generated VBA that initiated RefreshAll and then sent a message but didn't wait for the Refreshes to complete. So I then requested "Will that script wait for the RefreshAll to complete?" and got the response above. The response had a very detailed explanation as well as the code needed. I only added the item about the Pivot Tables/Charts above because I've worked on this issue in the past and knew that they needed their own refresh.

NOTE: I have not tested the code at all, so use at your own risk! I would suggest making a copy of the workbook you need it for and trying it on the copy before putting it into your final workbook.

Hope that helps!
Thanks for this unfortunately before posting here I tried unsucessfully about 10 chat gpt code suggestions 🤦‍♂️ its been very hit and miss and I would actually like to learn the background of vba rather than cheat although it has been helpful in some ways.
 
Upvote 0
Is my earlier open work book procedure conflicting with yours?
Yes, just as Rory explained above, use the modMain.initQueryTables line at the bottom line of your Workbook_Open event sub. So, there will be only one Workbook_Open.
 
Upvote 0

Forum statistics

Threads
1,223,367
Messages
6,171,671
Members
452,416
Latest member
johnog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top