Refresh Web Link in EXCEL

[thunderfoot]

I've linked 2 tables from the Web using EXCEL's Data/Get External Data/New Web Query to the worksheet that BA is downloading data too. I've put them on the same worksheet as I thought it would make coding easier, however, I would prefer to have the 2 tables going to their own worksheets, say, Sheet 2 & Sheet 3, Sheet 1 being where the BA data is downloaded too.

Individually these 2 tables can be refreshed by right-clicking in them and selecting Refresh Data.

I want these tables to be automatically refreshed everytime BA refresh's it's data on the worksheet. I have the following code in the Worksheet that BA is downloading it's data too:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("DataChange")) Is Nothing Then
    MyMacro
    End If
End Sub


..which basically operates a Macro Module ('MyMacro') everytime the BA data changes.

What code would the 'experts' suggest I use to make the 2 tables refresh everytime the BA data refresh's? Add code to the above or add code to the Macro Module?

[Mitch]

I'd write a new macro and call it from the above code.

As for what the code should be, easiest way is to use the macro recorder.

[Ian]

You could have a problem refreshing the web query every time BA updates. If you have the BA refresh every second it may take longer than 1 sec for the web queries to refresh and, then, you are sending another one before the previous one is finished. I've had problems with the screen freezing in the past.

[mrclox]

Private Sub Worksheet_Calculate()
If Range("a50").Value > 200 Then
Application.EnableEvents = False
my macro
Application.EnableEvents = True
End If
End Sub



then make cell a50 > 200 using formulas based on =now() or the ba time, i have mine triggered when the race has 20 secs till start

[Mitch]

How about creating a new macro and using Application.Ontime to refresh the table at safe intervals.

[GeorgeUK]

Just noticed this.

If you rightclick within the webquery data, one of the options is Data range properties
this will refresh at most, once per min.

With Selection.QueryTable
.RefreshPeriod = 1

not sure if there is a way to playaround with this.