VBA Wait for refresh of power query to execute next line of code

Question

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.

Option Explicit
Option Base 1


Public Sub LoadProductsForecast()

I have inserted a couple steps to optimise performance

'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

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.

ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
DoEvents

Below, is the remaining code that should run after the PowerQuery has refreshed the table:

'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" & 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 & ":" & 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

Answer 1

If your connection is OLEDB or ODBC you can set the background refresh temporarily to false - forcing the refresh to happen before code can continue on. Instead of calling

.Connections("Query - tblAdjustments").Refresh

do something like this:

Dim bRfresh As Boolean

    With ThisWorkbook.Connections("Query - tblAdjustments").OLEDBConnection
        bRfresh = .BackgroundQuery
        .BackgroundQuery = False
        .Refresh
        .BackgroundQuery = bRfresh

    End With

this example assumes you have an OLEDB connection. If you had ODBC, just replace OLEDBConnection with ODBCConnection