Macro Application.OnTime TimeValue doesnt run all the time

[marcusz]

Hi,

I am new here. This is my first post.I am a Linux sysadmin originally. I am trying to help someone with a system to manage some inventory. I build some Excel workbooks that gather input from different sources in 1 master excel workbook. So far all good.
This master workbook needs to run a macro every night at 01.00h am, so the values are copied to the next sheet ( day ). The workbook has 31 sheets, 1 for every day.
Also I have another macro in this workbook that runs every 5 minutes to grab several cells, open another file, paste them there, and close that file.

The 5 minute macro works perfect, the macro that has to run at 01.00h am, runs perfect for several days, and then sometimes it just does nothing, or doesnt run. I have no idea why it fails to run sometime.
The Excel file is open 24/7

No error message or whatever, it jut appears the next morning it didnt run.
Is there any way to debug this ?

Thanks in advance and also thanks for letting me drop in here and start asking for help immediatelty.

 

[offthelip]

Well done you spotted the error which stopped it working!! I did state that it was untested!!
I am pleased to hear it is all working Ok now!!

 

[marcusz]

Well done you spotted the error which stopped it working!! I did state that it was untested!!
I am pleased to hear it is all working Ok now!!

hmm, did some testsyesterday by setting the time . It worked all tests. Then set the time to 01.00am
Checked this morning and it didnt run .

I was celebrating to soon. Still there is a minor flaw in the code it seems

Public lasttime
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "Scheduler"
End Sub

Sub Scheduler()
tim = Time()
tim1 = TimeValue("01:00:00")
limt = TimeValue("00:03:00")  ' this is where you need to change the 30 seconds to 3 minutes
deltatim = Abs(tim - tim1)
If deltatim < limt Then
 lastlimt = TimeValue("00:10:00") ' 10 minute limit
 deltalast = Abs(tim - lasttime) ' calculate time since last call
  If deltalast > lastlimt Then ' check time since last call is greater than 10 minutes
   Call Master_IV_copy
   lasttime = tim
  End If
End If
Call Collect_Save
End Sub

 

[marcusz]

Just to be complete, this is Collect_Save module

But this one runs perfectly in the example used above. It rns perfectly in all tests so far.
The only doubt I have had is if the 5 min interval code in Collect_Save is duplicate as it is also used in ThisWorkbook code

Sub Collect_Save()

Call Collect

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:05:00"), "Collect_Save"

Call IV_status_creator

End Sub

Thanks

 

[offthelip]

Your problem is because you only call "scheduler" once ( when workbook open is called) after that you keep calling "collect save" so Copy_master_IV_copy " will never be called, you need to change the application on time call to this:

Sub Collect_Save()


Call Collect


Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True


Application.OnTime Now + TimeValue("00:05:00"), [COLOR=#ff0000]"Scheduler"[/COLOR]


Call IV_status_creator


End Sub

 

[marcusz]

Gonna change that,

Thanks a lot for all your feedback. I am learning here.