set code to go off every x seconds

[TiTuS]

Hi, just working on a code but need to be able to get it to trigure itself every 5-10 seconds... is this possible?

 

[sbendbuckeye]

Can you explain a little bit what you are trying to do? Will you be continuing to work on your machine during this time? Will there be other applications running also? If you tie up the CPU with this process, is that a problem? Good Luck!

 

[TiTuS]

nah no problem if it ties up machine its so it can be going in the background, i may use a internet browser sometimes but it dosnt matter to much how many resources it uses... i know you cant do much while its going its so that i can leave a sheet refreshing so that if data changes on a site then it records the exact time it happened so while im away from keyboard i can open it and leave it monitoring

 

[TiTuS]

a button for turning it on and off could be handy to though

 

[Nimrod]

If the source of your data is from a web site and you've used the Website import wizard found on the Data toolbar then the refresh time is a built in property of the import.
Eg. Once you've set up your import data sheet just "right click" on sheet and select "Data Range Properties" from the drop down menu. In the "properties window you can set how ofter you want the refresh to occur.

 

[TiTuS]

only problem is it only goes down to 1 minute, im wanting under a minute updates

 

[Nimrod]

If the 1 minute refresh is to long then here's some code that will do the trick... however it will tie up that particular instance of the excel app.

the following example refreshes web info on Sheet1

Sub LoopRefresh()
StartHere:
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

With ThisWorkbook.Sheets("Sheet1").Cells
.QueryTable.Refresh BackgroundQuery:=False
End With

GoTo StartHere
End Sub

NOTE : TO TERMINATE CODE PRESS CTRL+ ALT + BREAK


PS.... The wait code is directly from the VBEditors help file....

Wait Method Example

This example pauses a running macro until 6:23 P.M. today.

Application.Wait "18:23:00"
This example pauses a running macro for approximately 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
This example displays a message indicating whether 10 seconds have passed.

If Application.Wait(Now + TimeValue("0:00:10")) Then
MsgBox "Time expired"
End If

 

[TiTuS]

Thankyou so much! thats exactly what i needed.. and thankyou very much for showing me steps now i can see how its done and next time be able to

 

[Nimrod]

Your Welcome .... glad I could help