How to make Excel recalculate every ??? seconds

Posted by David Rees on September 11, 2000 1:54 AM

I have some sheets that go off to a database and retrieve data.

Is it possible to get Excel to recalculate at specified intervals (3 minutes would be ideal) so I can keep reading the latest data in?

Thanks,

David

Posted by JAF on September 11, 0100 3:08 AM

David

The following in a normal module should do the trick.

Dim nextRun As Date
Option Explicit

Sub StartMacro()
'Run this macro to start "YourMacro"
Call ReRun
End Sub

Sub ReRun()
'This automatically runs "YourMacro" each specified interval
nextRun = Now + TimeValue("00:03:00")
Application.OnTime nextRun, "ReRun"
Call YourMacro
End Sub

Sub YourMacro()
'YOUR CODE HERE
End Sub

Sub Auto_Close()
'Turns off the OnTime event when closing the file
Call StopMacro
End Sub

Sub StopMacro()
'Run this to turn off the OnTime event
On Error Resume Next
Application.OnTime nextRun, "ReRun", schedule:=False
End Sub