Automatically update data connections in Excel VBA

The following example will allow you to refresh the data connections every time a specific sheet is opened or even schedule it to run at regular intervals. The core idea is to use the Workbook.Connections object to access all data connections and refresh them.

Objective of the VBA Code:

1. Automatically update all data connections in the workbook.
2. Ensure all connections are refreshed before working with the data.
3. Use a workbook open event or a button to trigger the refresh.

VBA Code to Refresh Data Connections

1. Refresh Connections when the Workbook is Opened (Using Workbook_Open Event)

If you want to update data connections automatically every time the workbook is opened, you should add the following code inside the ThisWorkbook module.

Steps:

1. Open the VBA editor by pressing Alt + F11.
2. In the « VBAProject » panel, find « ThisWorkbook » and double-click it to open the code window.
3. Copy and paste the following code into the code window.

Private Sub Workbook_Open()
    ' Call the function to refresh all data connections
    UpdateAllConnections
End Sub

Sub UpdateAllConnections()
    Dim conn As Object
    ' Loop through all data connections in the workbook
    For Each conn In ThisWorkbook.Connections
        ' Try to refresh each connection
        On Error Resume Next ' Ignore error if connection fails
        conn.Refresh
        On Error GoTo 0 ' Reset error handling
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• Workbook_Open: This is an event that is automatically executed when the workbook is opened. It calls the UpdateAllConnections function that refreshes all connections.
• UpdateAllConnections: This function loops through all data connections in the workbook (ThisWorkbook.Connections) and attempts to refresh them using conn.Refresh.
• On Error Resume Next: This line ensures that any errors (like if a connection cannot be refreshed) are ignored.
• MsgBox: A message box is displayed once all connections have been refreshed.

2. Refresh Connections Using a Button

If you prefer to have a button on a worksheet to trigger the refresh of connections, follow these steps:

Steps:

1. In your Excel sheet, insert a form button (via the Developer tab > Insert > Button).
2. Link the button to a macro that will refresh the connections.
3. Create a standard module to add the following code.

VBA Code:

Sub RefreshConnections()
    Dim conn As Object
    ' Loop through to refresh all connections
    For Each conn In ThisWorkbook.Connections
        ' Try to refresh each connection
        On Error Resume Next
        conn.Refresh
        On Error GoTo 0
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• This code is very similar to the one used in the Workbook_Open event, except it is triggered manually through a button.
• The RefreshConnections procedure is linked to the button you inserted in the worksheet. When the user clicks the button, all connections are refreshed.

3. Automatically Refresh Connections at Regular Intervals (Optional)

If you want the connections to refresh automatically at regular intervals, you can use the following code, which should be placed in a standard module.

VBA Code for Regular Interval Refresh:

Dim NextRefresh As Date
Sub StartAutoRefresh()
    ' Start automatic refresh every X minutes
    NextRefresh = Now + TimeValue("00:05:00") ' Refresh every 5 minutes
    Application.OnTime NextRefresh, "AutoRefresh"
End Sub

Sub AutoRefresh()
    ' Refresh all connections
    Call UpdateAllConnections
    ' Restart automatic refresh
    Call StartAutoRefresh
End Sub

Sub UpdateAllConnections()
    Dim conn As Object
    ' Loop to refresh all connections
    For Each conn In ThisWorkbook.Connections
        On Error Resume Next
        conn.Refresh
        On Error GoTo 0
    Next conn
    MsgBox "All connections have been updated!", vbInformation
End Sub

Code Explanation:

• StartAutoRefresh: This macro starts the auto-refresh process by setting the next refresh time using Application.OnTime.
• AutoRefresh: This macro is automatically called at regular intervals to refresh all connections.
• NextRefresh: This variable defines the time for the next refresh (here, it’s set to 5 minutes after the first execution).

Conclusion:

You now have a complete VBA code for automatically updating data connections in an Excel workbook. You can run it on workbook open, via a button, or on a scheduled timer at regular intervals.