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

if you truely just need the vba to wait for a bit while the query refreshes, you could try a simple call to the Application's wait method. For example, putting this right below your query refresh line would cause the code to wait for 10 seconds: Call Application.Wait(Now + TimeValue("0:00:10")) — ArcherBird
I need it to be dynamic, as the query will need to run often, and amount of data (thus data refresh time) will vary a lot. — Tue Herlevsen

ArcherBird ArcherBird · Accepted Answer · 2018-11-19T15:01:33

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

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

3 Answers