We are using PowerQuery as Excel 2010 Add-In. We are new to this but I am creating processes that other less expert users use.
I have a query that merges and compares multiple files leading to the updating of two excel tables.
I then have a pivot table that is based on one of these tables.
Where they are not fixed, I use parameters stored in the workbook to hold the filenames and paths that are the source of the queries.
To ensure that the pivots update after the table has refreshed I use a macro:
All works as intended UNTIL..... One of the filepath parameters was entered incorrectly. The query then failed to refresh but the user still got the message suggesting that the operation had completed. The Tables did not update or return no data or anything that would be easy to spot through normal checks. The best I can do at present is to tell them to have the Query Pane Open and look out for "Download did not complete" or other errors. What I would prefer via PowerQuery or VBA is to be able to provide a clear message about whether the refresh was successful (and ideally what went wrong). Can anyone help please?
I have a query that merges and compares multiple files leading to the updating of two excel tables.
I then have a pivot table that is based on one of these tables.
Where they are not fixed, I use parameters stored in the workbook to hold the filenames and paths that are the source of the queries.
To ensure that the pivots update after the table has refreshed I use a macro:
Code:
With ThisWorkbook .RefreshAll
Application.CalculateUntilAsyncQueriesDone
For Each pc In .PivotCaches
pc.Refresh
Next pc
End With
ans = MsgBox("Queries have been refreshed and then pivot tables")All works as intended UNTIL..... One of the filepath parameters was entered incorrectly. The query then failed to refresh but the user still got the message suggesting that the operation had completed. The Tables did not update or return no data or anything that would be easy to spot through normal checks. The best I can do at present is to tell them to have the Query Pane Open and look out for "Download did not complete" or other errors. What I would prefer via PowerQuery or VBA is to be able to provide a clear message about whether the refresh was successful (and ideally what went wrong). Can anyone help please?
