PDA

View Full Version : Refreshing Queries using VBA - Message Alert for Queries that Failed to Refresh



jdorbish
2019-01-08, 05:20 PM
Hi,

I have the below code that refreshes a workbook that uses a power query data model as it's source. My issues is that, even when the queries fail (if another person has one of the source workbooks open, the file path is wrong for one of the query connections, I have a many to one relationship in my dimensions table, etc.), the vba code finishes and acts as if the query refreshed successfully. It's not until I run the query manually that I receive a popup showing the query produced an error that I'm aware the query didn't run successfully (picture of error attached). Is there vba code I can add that will alert me if the query doesn't finish successfully? Thanks!8767


Dim Connection As WorkbookConnection

For Each Connection In ThisWorkbook.Connections
Connection.Refresh
Next Connection
Dim qt As QueryTable
For Each wks In ActiveWorkbook.Worksheets
For Each qt In wks.QueryTables
qt.Refresh
Next qt
Next wks


ActiveWorkbook.RefreshAll