title: VBA Wait for refresh of power query to execute next line of code
body: <p>I am working on a VBA project, that requires update of a specific table via power query as part of the code.
  The code power query refresh needs to finish, before the query continues, but, i have not managed to find a solution to do that yet.</p>
  
  <pre><code>Option Explicit
  Option Base 1
  
  
  Public Sub LoadProductsForecast()
  </code></pre>
  
  <p>I have inserted a couple steps to optimise performance</p>
  
  <pre><code>'Deactivate global application parameters to optimise code performance
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayStatusBar = False
  
  
  'Dimensions used in code for sheets etc.
  Dim lastrow As Integer
  Dim NoRowsInitial As Integer
  
  
  ''''''''''''''''''''''
  ''Get product data, and copy index match formula to look up the forecast
  
  ' find number of rows to use for clearing
  NoRowsInitial = WorksheetFunction.CountA(Worksheets("Monthly Forecast").Range("D4:D15000"))
  
  'Selecting Worksheet w. product master data
  Dim wb As Workbook
  Dim ws As Worksheet
  Set wb = ActiveWorkbook
  Set ws = Sheets("Products")
  wb.Activate
  ws.Select
  </code></pre>
  
  <p>The next line is where I wish to refresh the power query, and the refresh part works as it should.
  However, it countinues to run the next VBA code. I have searched for different answers online, and some refer to "DoEvents", however, it does not seem to make a difference.</p>
  
  <pre><code>ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
  DoEvents
  </code></pre>
  
  <p>Below, is the remaining code that should run after the PowerQuery has refreshed the table:</p>
  
  <pre><code>'Calculating number of rows to copy
  lastrow = WorksheetFunction.CountA(Worksheets("Products").Range("B4:B15000"))
  
  'Copying rows
  Worksheets("Products").Range(Cells(4, 2), Cells(lastrow + 3, 10)).Copy
  
  'Selecring forecast sheet
  Set ws = Sheets("Monthly Forecast")
  ws.Select
  
  'Disabling alerts, so pop up for pasting data does not show (activated again later)
  Application.DisplayAlerts = False
  
  'Pasting product master data
  Worksheets("Monthly Forecast").Range(Cells(8, 4), Cells(lastrow, 12)).PasteSpecial
  
  
  'Creating a string that contains range to paste formula in to
  Dim RangeString As String
  RangeString = "N8:W" &amp; lastrow + 7
  
  'Copying formula to paste
      Range("AJ2:AJ3").Select
      Selection.Copy
  
  'Pasting formula that looks up baseline FC (both seasonal and SES)
      Range(RangeString).Select
      ActiveSheet.Paste
  
  Calculate
  
  With Range(RangeString)
      .Value = .Value
  End With
  
  'Activating alerts again
  Application.DisplayAlerts = True
  
  
  
  ''''''''''''''''''''''
  ''Code to clean the rows that are not used
  'Remove unescessary rows
  
  
  Dim NPIProducts As Integer
  NPIProducts = [tblNewProd].Rows.Count
  
  
  'tbl.Range.Rows.Count
  
  
  
  Dim RowsToDelete As String
  
  RowsToDelete = lastrow + NPIProducts * 2 &amp; ":" &amp; NoRowsInitial
  
  If Left(RowsToDelete, 1) = "-" Then
      'do nothing (negative)
  Else
      [tblMonthly].Rows(RowsToDelete).Delete
  End If
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  '''''''''''''''''''''''''''''''''''''''''''''
  '''''''''''''''''''''''''''''''''''''''''''''
  ''''End of main code
  
  'Activate global application parameters again
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.DisplayStatusBar = True
  
  
  'Messages end user that the calculation is done
  MsgBox "Load of products and forecast finished"
  
  End Sub
  </code></pre>