How do to run VBA code after an automatic data connection update that has been set to run every 5 minutes?

Question

I am trying to have a data connection to a website that will automatically update every 5 min.  After this update I would like to have a VBA Macro run to analyze the new data.  does anyone know how to have the code run after that Auto Update?

Answer 1

You could use the Worksheet_Change event to detect when the data is updated, and then run your macro.  Simple example below assumes you have a QueryTable (i.e. data connection to the web) on Sheet1.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myConn As QueryTable

    Set myConn = Me.QueryTables(1)

    With myConn

        If (Not Intersect(Target, Range(.Destination.Address)) Is Nothing) Then

            MsgBox "The data was updated"

            Call MyMacro(myParam1, myParam2)

        End If

    End With

End Sub

HTH,

Eric