RefreshAll ODBC

gemini528

Board Regular
Joined
Jun 13, 2013
Messages
53
Code:
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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Chip Pearson has a bunch of info on error handling. Error Handling In VBA

Code:
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
 
Upvote 0

Forum statistics

Threads
1,214,391
Messages
6,119,244
Members
448,879
Latest member
VanGirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top