Auto Refresh

[Shaun]

Hi.

I need a macro for auto refresh at a set time. i have imported external data to use with the software for other markets but the best i can get excel to do is 1 minute refresh, i would like to set the macro to refresh at a minimum 10 seconds any help please, the Mr Excel site is down.

[Ian]

Application.Ontime is the command you need.

[Shaun]

I found this code on the internet and it works very well, but is there a way to input data on one sheet while another sheet updates the 2 sheets data is linked.

Code: Select all

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

Code: Select all

'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("Data")

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:05")
    On Error Resume Next
    Application.OnTime RunWhen, "RefreshQuery"
    On Error GoTo 0
End If

End Sub

[Ian]

Do you have the web queries in more than one sheet ? If so, you can use ActiveWorkbook.RefreshAll to refresh them all in one go.

[Shaun]

They are only on one sheet the other sheet uses them as a reference, but when i have the data sheet updateing i can't type on the other sheet.

Anyway to have the Data sheet silintly update like BA does and still alowe me to type.

[Ian]

I don't think you can. In fact, whilst you are refreshinng the web query, I don't think BA is updating the sheet either.