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
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.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)
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
BeforeUpdate
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. WorkbookConnection
object events by using WithEvents
in a class 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
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
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
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.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:
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!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
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!