VBA Refresh of Data Model - How to detect an error

[LGXSteve]

I have written a small bit of VBA to allow me to have a button on the front worksheet to refresh the Data Model.

The code looks like this;

Sub Button2_Click()
ActiveWorkbook.Model.Refresh
End Sub

There are occasions where a fault in the data means that this will fail. if I do the refresh through the 'Manage' Screen I can see the error, and correct it.

Is there anyway of detecting if the above has failed, and at least alert the user there was a problem?

(Arguably the data should not be able to get into a state which would cause this to fail, but it does. Detecting the error, and manually fixing it is probably an easier solution for the time being).

Thanks

 

[maxxam]

Finally I can solve a multi-year old request in the hope it may sometime help someone like so many other posts have helped me.

Sheet.Unprotect 'Important otherwise the refresh table will not work if it is a protected sheet
Sheet.PivotTable(1).RefreshTable

This will produce the same error message that you get when refreshing a pivot table manually that is linked to a data model. Just add this for anyone of your tables linked to the data model, no need to run it for all.