I am writing a macro to refresh all queries in my workbook and want to handle errors during the refresh like this with something along the lines of:
But this doesn't seem to work - even if an error is encountered, Err.Number returns 0.
On the other hand, if I refresh a connection known to have an error manually like this,
ActiveWorkBook.Connections("MyConnection").Refresh
Then the above error handling does work.
If I try to Refresh All manually (through the Excel ribbon), an error does appear - and then it prompts me if I want to continue ("One or more connections didn't refresh or the refresh operation was cancelled. Would you like to refresh the remaining connections").
What I really want is for VBA to detect that error and then cancel the remaining refreshes without the user needing to do it.
I would refresh to use Refresh All rather than looping through the queries but if that's only way then I guess it's not the end of the world.
Code:
On Error Resume Next
ActiveWorkBook.RefreshAll
If err.Number <> 0 Then
Msgbox "Errors detected"
Else
Msgbox "No errors"
End ifBut this doesn't seem to work - even if an error is encountered, Err.Number returns 0.
On the other hand, if I refresh a connection known to have an error manually like this,
ActiveWorkBook.Connections("MyConnection").Refresh
Then the above error handling does work.
If I try to Refresh All manually (through the Excel ribbon), an error does appear - and then it prompts me if I want to continue ("One or more connections didn't refresh or the refresh operation was cancelled. Would you like to refresh the remaining connections").
What I really want is for VBA to detect that error and then cancel the remaining refreshes without the user needing to do it.
I would refresh to use Refresh All rather than looping through the queries but if that's only way then I guess it's not the end of the world.
Last edited:
