Mixing
VBA Wait for refresh of power query to execute next line of code
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
excel vba excel-vba refresh powerquery
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
add a comment |
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
excel vba excel-vba refresh powerquery
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
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'swaitmethod. 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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:44
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49
add a comment |
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
excel vba excel-vba refresh powerquery
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
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
excel vba excel-vba refresh powerquery
excel vba excel-vba refresh powerquery
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
asked Nov 19 '18 at 14:30
Tue Herlevsen
152
152
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'swaitmethod. 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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:44
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49
add a comment |
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'swaitmethod. 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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:44
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49
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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:44
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
Nov 19 '18 at 14:44
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49
add a comment |
3 Answers
3
active
oldest
votes
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
answered Nov 19 '18 at 15:01
ArcherBird
773219
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
add a comment |
If you haven't already, disable background refresh for the query (plus any queries that precede that query in the evaluation chain).
You'll want to make sure that the background refresh option is not ticked. I accessed this window by right-clicking the query and then clicking Properties. I think in some other Excel versions, you might instead need to go to Data > Connections, find the query in the list and then edit its properties there.
answered Nov 19 '18 at 14:48
chillin
1,249134
add a comment |
This is untested but in theory it should work.
Split your code in two parts.
The first part ends with the refresh.
sub some_sub()
'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
ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
end sub
Then in order to wait for it to finnish we let the sub run to end.
Then we let Excel fire the Worksheet_Change.
On the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'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
You can use Target to not make it run if you don't want to. I assume there is at least one cell that you know will change. Set the target there.
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376784%2fvba-wait-for-refresh-of-power-query-to-execute-next-line-of-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 19 '18 at 15:01
ArcherBird
773219
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
add a comment |
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
answered Nov 19 '18 at 15:01
ArcherBird
773219
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
add a comment |
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
answered Nov 19 '18 at 15:01
ArcherBird
773219
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
answered Nov 19 '18 at 15:01
ArcherBird
773219
answered Nov 19 '18 at 15:01
ArcherBird
773219
answered Nov 19 '18 at 15:01
ArcherBird
773219
answered Nov 19 '18 at 15:01
ArcherBird
773219
773219
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
add a comment |
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
1
1
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
When Power Query loads data into a table in Excel it does so by setting up an OLE DB connection between itself and the table. So as a general rule for working with Power Query refreshes and macros, this is how you want to handle it.
– Wedge
Nov 19 '18 at 16:22
add a comment |
If you haven't already, disable background refresh for the query (plus any queries that precede that query in the evaluation chain).
You'll want to make sure that the background refresh option is not ticked. I accessed this window by right-clicking the query and then clicking Properties. I think in some other Excel versions, you might instead need to go to Data > Connections, find the query in the list and then edit its properties there.
answered Nov 19 '18 at 14:48
chillin
1,249134
add a comment |
If you haven't already, disable background refresh for the query (plus any queries that precede that query in the evaluation chain).
You'll want to make sure that the background refresh option is not ticked. I accessed this window by right-clicking the query and then clicking Properties. I think in some other Excel versions, you might instead need to go to Data > Connections, find the query in the list and then edit its properties there.
answered Nov 19 '18 at 14:48
chillin
1,249134
add a comment |
If you haven't already, disable background refresh for the query (plus any queries that precede that query in the evaluation chain).
You'll want to make sure that the background refresh option is not ticked. I accessed this window by right-clicking the query and then clicking Properties. I think in some other Excel versions, you might instead need to go to Data > Connections, find the query in the list and then edit its properties there.
answered Nov 19 '18 at 14:48
chillin
1,249134
If you haven't already, disable background refresh for the query (plus any queries that precede that query in the evaluation chain).
You'll want to make sure that the background refresh option is not ticked. I accessed this window by right-clicking the query and then clicking Properties. I think in some other Excel versions, you might instead need to go to Data > Connections, find the query in the list and then edit its properties there.
answered Nov 19 '18 at 14:48
chillin
1,249134
answered Nov 19 '18 at 14:48
chillin
1,249134
answered Nov 19 '18 at 14:48
chillin
1,249134
answered Nov 19 '18 at 14:48
chillin
1,249134
1,249134
add a comment |
add a comment |
This is untested but in theory it should work.
Split your code in two parts.
The first part ends with the refresh.
sub some_sub()
'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
ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
end sub
Then in order to wait for it to finnish we let the sub run to end.
Then we let Excel fire the Worksheet_Change.
On the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'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
You can use Target to not make it run if you don't want to. I assume there is at least one cell that you know will change. Set the target there.
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
add a comment |
This is untested but in theory it should work.
Split your code in two parts.
The first part ends with the refresh.
sub some_sub()
'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
ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
end sub
Then in order to wait for it to finnish we let the sub run to end.
Then we let Excel fire the Worksheet_Change.
On the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'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
You can use Target to not make it run if you don't want to. I assume there is at least one cell that you know will change. Set the target there.
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
add a comment |
This is untested but in theory it should work.
Split your code in two parts.
The first part ends with the refresh.
sub some_sub()
'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
ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
end sub
Then in order to wait for it to finnish we let the sub run to end.
Then we let Excel fire the Worksheet_Change.
On the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'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
You can use Target to not make it run if you don't want to. I assume there is at least one cell that you know will change. Set the target there.
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
This is untested but in theory it should work.
Split your code in two parts.
The first part ends with the refresh.
sub some_sub()
'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
ActiveWorkbook.Connections("Query - tblAdjustments").Refresh
end sub
Then in order to wait for it to finnish we let the sub run to end.
Then we let Excel fire the Worksheet_Change.
On the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'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
You can use Target to not make it run if you don't want to. I assume there is at least one cell that you know will change. Set the target there.
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
answered Nov 19 '18 at 14:48
Andreas
15.1k31441
15.1k31441
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376784%2fvba-wait-for-refresh-of-power-query-to-execute-next-line-of-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
waitmethod. 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
Nov 19 '18 at 14:40
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
Nov 19 '18 at 14:44
is your connection either an OLEDB or ODBC?
– ArcherBird
Nov 19 '18 at 14:49