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

[gazmoz17]

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

 

[jdellasala]

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:

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!

 

[RoryA]

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)

 

[jdellasala]

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.

 

[RoryA]

It won't even compile as written because ThisWorkbook.ForceFullCalculation is a property, not a method.

 

[smozgur]

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.

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.

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:

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.

 

[gazmoz17]

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.

Is my earlier open work book procedure conflicting with yours?

Many Thanks
Gareth

 

[gazmoz17]

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:

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.

 

[RoryA]

Is my earlier open work book procedure conflicting with yours?

Yes, it is. Move the one processing line from Suat's code to the end of your existing Open event.

 

[smozgur]

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.