Error handling Power Query refresh with VBA

Question

I have a VBA code that refreshes my data connections twice and then calculates the workbook. I added On Error Resume Next, but when there is an error with the query refreshing it ignores my VBA error handling and shows an error saying "Unexpected Error, Something went wrong. If the problem continues, please restart Excel." After i click cancel it continues with my code but the one query that caused the error still shows its running even though the other queries ran after it. I turned off background refresh on all connections already and i am just trying to find some VBA code where i can handle the error with power query.

Eventually i would want my code to cancel the query that caused the error and refresh it individually after all the code is done.

How do we handle errors in power query with VBA?

Current code:

On Error Resume Next 
ActiveWorkbook.RefreshAll 
ActiveWorkbook.RefreshAll 
ActiveWorkbook.Calculation = xlAutomatic

I think I would need to see your code to help with this. Could you add it to the question? — Mistella
Error handling in Power Query should be handled in Power Query, not VBA. However if something is going wrong enough it's causing a message like that, it's likely something that needs to be addressed and not ignored (I'll sometimes see a message like that if Power Query is capping out the available system memory). — Wedge
On Error Resume Next will capture errors thrown by the VBA compiler, not the Excel application. — dwirony
the code is pretty simple On Error Resume Next ActiveWorkbook.RefreshAll ActiveWorkbook.RefreshAll ActiveWorkbook.Calculation = xlAutomatic — TimQ

Jonas S Jonas S · Accepted Answer · 2019-03-06T11:42:17

Within Power Query you can wrap you code as (assuming this is Query1):

try
  let
  ....

  in
  ...

otherwise
  "Error"

and then having one error wrapping query for all queries as:

if Query1 = "Error" then "Error" 
else if Query2 = "Error" then  "Error"
...
else "No Error"

which you can then load to Excel as a new sheet and check with VBA macro whether the value of such a cell contains "Error" or not.

Error handling Power Query refresh with VBA

1 Answers