RefreshAll ODBC

[gemini528]

I have a pre-configured ODBC connection and come up with the Macro below to REFRESH data. It is working fine but if I am using a workstation that is not configured to ODBC I got an error. I need a revision on the code below to trap the error, instead of an error there will be a message that “This workstation is not configured to ODBC”. I am a newbie and any improvement on the code below is most welcome.

Thank you.

[Sub RefreshAll()
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False

    Dim conn As Variant
  
      For Each conn In ActiveWorkbook.Connections
         conn.ODBCConnection.BackgroundQuery = False
      Next conn

      ActiveWorkbook.RefreshAll
    
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

End Sub
/CODE]

 

[CalcSux78]

Chip Pearson has a bunch of info on error handling. Error Handling In VBA

Sub RefreshAll()


On Error GoTo NoRefresh:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False


Dim conn As Variant


For Each conn In ActiveWorkbook.Connections
conn.ODBCConnection.BackgroundQuery = False
Next conn


ActiveWorkbook.RefreshAll


NoRefresh:
If Not Err.Number = 0 Then MsgBox "This workstation is not configured to ODBC"
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub