Refreshing Queries using VBA - Message Alert for Queries that Failed to Refresh

Status
Not open for further replies.

jdorbish

New member
Joined
Jan 8, 2019
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Professional Plus 2016
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!Error.PNG

Code:
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
 
Status
Not open for further replies.
Back
Top