How to loop a macro every 20 minutes

Hot Topic

To find Whether a value in a cell is pasted?
Normal.dot file for word 2003 or version 11.0
Get Excel Sheet Names
Applying InsertBreak to a style?
programmaticaly invoking the desktop alert
Reversing a lists order in excel for my macro
pos peripherals from vba
Excel - email macro help
Closing desktop opened word wants to close embedded word object
Runtime Error '2001'

Microsoft ISV

When sending a worksheet to the USB port …
Find and Replace Carriage Return in Excel with VBA
Excel function PRICE
Loop til you drop
InsertDatabase is returning error "Word was unable to open the data source"
Using VBA macro to locate next free column on worksheet
opc
Internet Explorer 7 jpeg and gif blurry after tools/internet options/advance/reset
Problem: Macro not deleting columns I select
Creating data with a loop based on current info in worksheet

How to loop a macro every 20 minutes

Hello,

I need assistance with some code that will run this macro every 20 minutes in an Excel workbook that is constantly open. I think it needs to loop somehow but it is beyond my programming skills. If anyone can point me to the right Method I would greatly appreciate it!!

Michael


Answer this question

How to loop a macro every 20 minutes

  • e.henriquez

    I am not too sure if I can help but you need to make a list of procedures that will initiate upon opening the file. In the procedures send a loop that would repeat infinitely and only "Call UpdateStats()" when the time reaches a divisible of twenty.


  • Heino

    Thanks Derek

    ChasAA

  • Mark Beiley

    Nice one ChasAA.

  • Donal McWeeney

    The other option is to send the process to sleep for 20 minutes using the Windows API.

    http://mech.math.msu.su/~vfnik/WinApi/s/sleep.html

    Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

    Sleep 2000 ' 2000 milliseconds = 2 seconds to delay

    It might not be the solution your looking for it reallt depends on what you have in mind.



  • jschumann.net

    Hello Michael,

    Play around with the following and you should be able to get the result you need.

    ChasAA

    [Code starts]

    Sub start()
    ' Put this in your workbook open event
    Application.OnTime Now + TimeValue("00:00:15"), "runUpdate"
    ' I have set it for 15 seconds in this example change to "00:20:00" for 20 minutes
    End Sub


    Sub runupdate()
    ' this is where your code for the updating will go
    ' the following two lines are only so that I could see what was happening
    Selection.Value = "Last ran at " & Format(Now, "hh:mm:ss")
    Selection.Offset(1, 0).Select
    Call start ' make sure you add this line to start the timer again
    End Sub

    [Code ends]
  • How to loop a macro every 20 minutes
Answer this question