Macro to wait until "Refresh All" will be complete

[Igor R.]

Hi!
I have few excel files which must be proceeded every month. All I have to do is to open each file, update date and press "Refresh All".
Each file contain several Power Query queries and several Pivot Tables.
It's not much work, but still I want to make macro that opens each file and do the routine automatically.
The macro I made generally works, but because of refreshing take some time and my macro continue running before the refresh is completed I receive the next message:
"This will cancel a pending data refresh. Continue?"
I have tried several solutions founded in internet and even suggested by GptChat, but nothing really does the work.

Here is code that i want to run:

 ' Refresh Power Query queries
   Dim qt As WorkbookQuery
   For Each qt In srcWb.Queries
     qt.Refresh
   Next qt
                      
 ' Refresh Pivot Tables
   Dim pt As PivotTable
   Dim sht As Worksheet
   For Each sht In srcWb.Worksheets
     For Each pt In sht.PivotTables
       pt.RefreshTable
     Next pt
   Next sht

I tried next solutions, but nothing works:

' Wait until Power Query queries are completed
  Do Until Application.Ready And srcWb.ConnectionsDisabled And srcWb.QueryTableRefreshComplete
    DoEvents
  Loop

 ' Wait for the refresh to complete
   Application.Wait Now + TimeSerial(0, 0, 20)

  ' Wait for the refresh to complete
    Dim startTime As Double
    startTime = Timer
    Do Until Timer - startTime > 10 ' Adjust the timeout value as needed
       On Error Resume Next
       If Not Application.Ready Then Exit Do
       If srcWb.RefreshAllState = xlDone Then Exit Do
       On Error GoTo 0
       DoEvents
    Loop

Please, can somebody suggest something that will work?
Thanks in advance!

 

[BobBridges]

I never use pivot tables and therefore have no experience with the Refresh method, but I see that there's a Worksheet event named PivotTableUpdate that fires (it says here) "after a PivotTable report is updated on a worksheet". It doesn't say exactly what "update" means, but it might mean when a Refresh is completed. Probably worth a try, anyway.

If that doesn't work, I see a few other PivotTable events listed there too. Maybe one of those....