title: VBA: Refresh Power query and subsequently update pivot tables
body: <p>I have an Excel file that comprises a sheet &quot;DATA&quot;, which contains data extracted using a Power Query, and three other sheets containing pivot tables that are all dependent on these extracted data in &quot;DATA&quot;.</p>
  <p>I wantto create a VBA that (1) automatically refreshes/reruns the Power Query specified above when the Excel file is opened and (2) subsequently updates the three pivot tables to mirror the updated data.</p>
  <p>I have made several attempts and looked for similar questions on stackoverflow, but without succeeding:
  While it was possible for me to refresh the data by refreshing the connection, the Pivot tables were not updated accordingly.</p>
  <p>One VBA code that, among others, did not yield the desired results (i.e. subsequently updating the Pivot table) is:</p>
  <pre><code>Sub test()
  
  Dim ws As Worksheet
  Dim pt As PivotTable
  
  'ActiveWorkbook.RefreshAll 'make sure the refresh in bg property is false for all connections
   ActiveWorkbook.Connections(&quot;Name of Query&quot;).Refresh
  For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
          pt.RefreshTable
      Next pt
  Next ws
  
  End Sub
  </code></pre>
  <p>Thank you!</p>