VBA run macro at set times

  • Good morning,

    I am having difficulties with an excel/powerpoint connection.
    Both have a timed macro running, but both macro's acces the same excel database.
    As expected they run in to each other, and the powerpoint shuts down it's presentation due to the database being read only.
    I have tried having both macro's checking the file for being read only, this goes well with excel.
    Powerpoint shuts down the database after every update, and then it doesn't check anymore.
    i use

    With ActivePresentation

    So far the issue, now the solution i am looking for:
    Both macro's run every 10 minutes, but the ppt macro is so slow it is caught by excel.
    So is there a way to tell the macro's to run on X:05 -> X:15 -> X:25 -> etc.
    The other one i will run than at X:00 -> X:10 -> X:20 -> etc
    This why it doesn't matter the ppt macro takes 2 min.
    something like

    Sub M_snb_ontime_start()
    Application.OnTime TimeSerial(12, 45, 0), "M_snb"
    End Sub

    will work, but takes 6*24 lines of code a macro.

    Example files can be supplied, but didn't think they would be needed.

    with kind regards

  • Re: VBA run macro at set times

    You could try this line:

    TimeToRun = Now + TimeValue("00:00:01")   '<-- hh:mm:ss    change as needed.

    Will only require this one line of code to fire your macro every X minutes.

  • Re: VBA run macro at set times

    Hello Logit,

    unfortenuatly that won't work, it is partly how it runs now:

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 300 ' 15 minutes
    Public Const cRunWhat = "SaveFile"  ' the name of the procedure to run
    Sub Auto_Open()
    '  sub automatically ran upon opening the file
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
    End Sub

    The issue is that the macro running calls a reset after running, this whay it doesn't always run at the same time.
    There is a time shift if the length of the macro, and the one in powerpoint takes actual minutes.

    this is why i am looking at timeserial in stead of timevalue
    kind regards

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!