Forum Home
Press F1
 
Thread ID: 54801 2005-02-22 19:47:00 How do I refresh Excel external data faster beetlebaily (7408) Press F1
Post ID Timestamp Content User
327447 2005-02-22 19:47:00 The fastest time you can set the "Get external data range properties" is 1 minute. This is not fast enough for my application. I would like to achieve a refresh of data every 15 seconds. Does anyone know how to accomplish this either in MS SQL, VBA, Excel or some other form. The Query name currently running is: "Query for line 501" Thank you for any advice. beetlebaily (7408)
327448 2005-02-23 01:02:00 Using the OnTime VBA function, a background query refresh can be run every 15 seconds, but the example below will run continuously once started.

The Start_Timer macro calls the query refresh macro which activates the worksheet which contains the query then runs the query in the background then calls the Start_Timer procedure for another 15 second wait.

Sub Start_Timer()
Application.OnTime Now + TimeValue("00:00:15"), "Refresh_Query"
End Sub

Sub Refresh_Query()
Sheets("myquerysheet").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Start_Timer
End Sub

This is quick and dirty, but HTH 		rad_s4 (7401)
327449 2005-02-23 01:19:00 To expand on rad_s4 very good answer you may wish to build in some control to stop the refreshing process. You can stop the code via CTRL+Break but its not very professional. Heres an example where Im monitoring the F12 key and when pressed it toggles refreshing on and off. Hopefully you can understand the commented code.

Also see Chip Pearsons comments on using the OnTime method. www.cpearson.com

Place in ThisWorkbook module

Private Sub Workbook_Open()
'Monitor the F12 key and when pressed run the ToggleRefresh procedure
Application.OnKey "{F12}", "ToggleRefresh"

'The refresh process will be off by default when the book is opened
'If you want it on then run this line below.
'Note because the RefreshOn variable is false by default and ToggleRefresh
'changes it to the opposite then RefreshOn will be changed to True and the
'RefreshQuery procedure will be called by the ToggleRefresh procedure.
Call ToggleRefresh

End Sub

Place in a standard module (eg Module1)

'A public variable that will stay in memory while the book is open
'The value of this variable will be False by default.
'Its used as an indicator so you know whether to refresh or not in the RefreshQuery procedure
Public RefreshOn As Boolean
Public RunWhen As Double

Public Sub ToggleRefresh()
'Toggle the refresh to the opposite that it is now
'ie if its currently false then the variable will now be true and visa versa
RefreshOn = Not RefreshOn

If RefreshOn = True Then
'If RefreshOn is true you want to start the refresh process
Call RefreshQuery
MsgBox "Web Query Refreshing is ON." & vbLf & vbLf & _
"To toggle refreshing ON/OFF press the F12 key.", vbInformation, "Web Query Refresh Status"
Else
'stop the pending ontime procedure
On Error Resume Next
Application.OnTime RunWhen, "RefreshQuery", schedule:=False
On Error GoTo 0
MsgBox "Web Query Refreshing is OFF." & vbLf & vbLf & _
"To toggle refreshing ON/OFF press the F12 key.", vbInformation, "Web Query Refresh Status"
End If

End Sub


Public Sub RefreshQuery()
Dim Qrytbl As QueryTable, Sh As Worksheet

'Change this to the name of your sheet that holds the web queries
Set Sh = Sheets("Sheet1")

For Each Qrytbl In Sh.QueryTables
'If the query isnt already refreshing then refresh it
If Not Qrytbl.Refreshing Then
On Error Resume Next
Qrytbl.Refresh False
On Error GoTo 0
End If
Next Qrytbl

'Repeat this procedure every 30 seconds. The false argument should clear
'the Ontime event if its in memory ready to run (ie stop it running twice in
'quick succession). You check to see if RefreshOn is true before repeating
'the procedure again
If RefreshOn = True Then
RunWhen = Now + TimeValue("00:00:30")
On Error Resume Next
Application.OnTime RunWhen, "RefreshQuery"
On Error GoTo 0
End If

End Sub 		Parry (5696)
327450 2005-02-23 01:25:00 Double post in error. Parry (5696)
1