Vba To Refresh Specific External Connections

[shayanan]

I have 3 External web Connections. I use code below to start and stop automatically refresh, but it refreshed All connections.

I need 2 Things:
1- just Refreshing One of Connections: "ConnectionA".
2- Use a cell value to Assign Seconds to repeat refreshing. for example value of cell "A2" is 45, so auto refresh every 45 second.



Public RefreshTime As Double
Public Const Seconds = 30 'Input Refresh Interval in seconds




Sub StartRefreshLoop()


'User Message indicating loop is beginning
MsgBox "Refreshes will begin to occur at " & _
"the designated interval of " & Seconds & " seconds"


'Call the first Refresh
Call StartRefreshes


End Sub




Sub StartRefreshes()


'Calculate Next Refresh Time
RefreshTime = Now + TimeSerial(0, 0, Seconds)


'Trigger a Refresh with OnTime function
Application.OnTime _
EarliestTime:=RefreshTime, _
Procedure:="RefreshConnections", _
Schedule:=True

End Sub




Sub RefreshConnections()


'Refresh Data Connections
ThisWorkbook.RefreshAll


'Start Timer Over Again
Call StartRefreshes


End Sub




Sub EndRefreshLoop()


'On Error Resume Next
Application.OnTime _
EarliestTime:=RefreshTime, _
Procedure:="RefreshConnections", _
Schedule:=False


'User Message indicating loop has ended
MsgBox "Refreshes are no longer occurring"


End Sub

[snb]

One of the properties of each 'web-connection' is its 'refresh rate'.
You con't need VBA to do the refreshing.

[shayanan]

You are right. best way to activate regularly refresh is on "Properties". but in this way it get refresh until you uncheck it again. I mean when you need 3 Hours a day to refresh 1 per minutes, and after that hours, you should disable update and do it day by day.
I was looking for an easy and more accessible way by press a button to start 30 second or 1 minuets update and after for example 15PM by pressing button refresh stopped.

[p45cal]

You're using refresh all in your code, you can refresh on a table by table approach, for example (depending on what kind of query it is):

Range("H2").ListObject.QueryTable.Refresh 'the query associated with a Table at a cell
Sheets("Sheet1").ListObjects("Table1_2").QueryTable.Refresh
Range("Table1_2").ListObject.QueryTable.Refresh 'don't need to refer to the sheet
'without a Table (listObject):
Range("A1").QueryTable.Refresh
Sheets("Sheet1").QueryTables(1).Refresh
Sheets("Sheet1").QueryTables("mytblName").Refresh