Any way to validate that Power Query refresh was successful?

[cr731]

I'm using Power Query and usually just use Refresh All to refresh all of my queries, and I would say that about 20-30% of the time, the query does not refresh successfully for whatever reason (it's pulling from an Access database and I frequently get the "File in use" error, which seems to go away if I do a second refresh).

Anyhow, I am looking for some way to identify that the refresh failed ... I'm using VBA to run the .RefreshAll command, so if there is a VBA way to determine if a query's refresh was a success, that would be ideal.

I'm using Excel 2013.

Is this possible?

 

[Matt Allington]

How do you know it fails? If you know, why do you need a VBA solution? Just asking!

 

[cr731]

How do you know it fails? If you know, why do you need a VBA solution? Just asking!

I know because if I open the Power Query pane, there is an exclamation next to the query saying that the Load to Data Model failed. Most often, if I attempt a refresh a second time, it works fine.

But I'd like a user friendly way to identify the refresh failure than having to open the pane and check each query.

 

[ImkeF]

I can only think of a way to generate this data in your queries. I'm not aware that there are metadata (at least not before 2016) that you can access for Power Query.

So you could add a column containing "DateTime.LocalNow", filling in the current datetime when the query was refreshed.
Harvest them and compare them - if there are deviations above a certain threshold (5 mins?) show some red flags using conditional formatting.