VBA to stop further processes if a Query has any errors

[johnny51981]

I'm working on updating some reports with VBA to allow for less hands on. Essentially, after I've acquired my source data in CSV format, I have a single button that refreshes the appropriate queries that update through PQ.

Now, to make sure I haven't mucked anything up or if there is anything wrong with the source data, what would be the appropriate VBA to notify me of an error in the query and to stop any further processes, such as my saving with the queries removed to the final landing spot for distribution?

EDIT:
Another scenario came to me. What would be the VBA that would provide me a notification of errors in a query but allows me to allow it to continue if I choose? One of my data sources has a mixed use column that may or may not have errors in it.

 

[gitmeto]

Have you taken a look at this site for error handling strategies?

VBA On Error - Error Handling Best Practices - Automate Excel

In this ArticleVBA Errors Cheat SheetErrorsVBA Error HandlingVBA On Error StatementOn Error GoTo 0On Error Resume NextErr.Number, Err.Clear, and Catching ErrorsOn Error GoTo LineVBA IsErrorIf Error VBAVBA Error TypesRuntime ErrorsSyntax ErrorsCompile ErrorsDebug > CompileOverFlow ErrorOther VBA...

www.automateexcel.com

 

[johnny51981]

How would I define the Power Query/Queries that have errors in the VBA?

 

[gitmeto]

Oh. I missed that in your problem discussion. What are the errors that you are receiving?

 

[johnny51981]

I don't receive them all of the time, so this is a preventative measure. The Power Queries (listed in Queries and Connections) can sometimes have errors due to formatting or I've messed something up along the way (such as the wrong file being saved with the wrong CSV file name).

 

[gitmeto]

One thing that I learned is that the data type has to be consistent both in the query and the spreadsheet. Are you familiar with changing the data type in the query editor?

 

[johnny51981]

Yes, very familiar. That is not the issue and the Power Query template is solid. The issue is to catch when mistakes happen to keep the report from finalizing.

Example:
Yesterday: Refreshed report, all came back clean with no errors, VBA allows report to complete and save as and where needed. Successfully complete.
Today: Two CSV Datasets were misnamed (One is named Two/Two is Named One), Refreshed report, resulting in errors. Desired result is to stop any further VBA from executing and report not saved as and where needed.

 

[gitmeto]

That seems like it would need to be configured in M Code to catch the errors while processing the query, which unfortunately is above my pay grade.