CODEWITHSUNDEEP
excelvbapowerquery
TimQ
TimQ

Reputation: 21

Error handling Power Query refresh with VBA

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

Upvotes: 2

Views: 5421

Answers (1)

Jonas S
Jonas S

Reputation: 101

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.

Upvotes: 0

Related Questions