Macro running problem

[cob98tp]

Good morning everybody,

I have set up a sheet which collects various different types of data throughout the day. At the end of each day, once everybody has gone home, I want a macro to run which then collates all this data and set up the sheet again for the next day. Mdusoe helped me with the following piece of code which runs 'Mymacro' at a preset time located in cell A1...

Public oldTime As Double

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If oldTime > 0 Then
On Error Resume Next
Application.OnTime oldTime, "MyMacro", , False
On Error Goto 0
End If
oldTime = [A1]
Application.OnTime [A1], "MyMacro"
End If

End Sub

This works fine.....sometimes. Unfortunately it has proved to be unreliable, running some nights and not others. Is this something to do with the way excel refreshes the time and can anything be done to make this more reliable?

Thanks.

 

[Harvey]

Application.OnTime TimeValue("17:00:00"), "my_Procedure"

has the code run at 5 pm. you can change this to whatever time you want. Change my_Procedure to the macro you want to run.

 

[cob98tp]

Thanks for the reply.

It seems a very easy way of solving my problem, but will it definitely work every single night? Even if nobody touches the computer for 2 or 3 days?

The reason I ask is that, as far as I can tell, the original code SHOULD always work as well but doesn't. I'm not sure this is down to the code but the way excel (97) works on my machine.... any clues anybody?

Thanks again.

 

[Harvey]

From what I see of your code is that it runs only when the worksheet changes, so people have to work with the sheet it order to have it "refresh". They also have to work with it after the desired time is elasped (and when they don't, nothing happens).

If you make a function, let's say DoRefresh:

public function DoRefresh()
  Wait(17:01)
  Application.OnTime TimeValue("17:00:00"), "DoRefresh"
  'code
end function

From what i know, this code should be executed each and every day, without exclusions, as long as the excel file stays open. You can put a check in the workbook_open to look if the code is executed on the given time, and if else execute it, and set a new timer.

 

[cob98tp]

Thanks for the continued help..

Unfortunately my VBA skills are sadly lacking and I'm unsure how to set up the code you suggest. Where do I need to insert the DoRefresh code? Also do I just use the first line of code you gave me as a replacement for my code in the first instance.. i.e. still in the Private Sub Worksheet_Change(..) routine in the Sheet code?

Sorry for the novice questions and thanks again for your help!

 

[cob98tp]

Ok, think I'm getting there...
Here is what I've put into the sheet module...

Public RunTime As Double
Public WaitTime As Double


Public Function DoRefresh()

RunTime = [A1]
WaitTime = [A1+(1/1440)]

Wait WaitTime
Application.OnTime RunTime, "DoRefresh"

End Function


Private Sub Worksheet_Change(ByVal Target As Range)

RunTime = [A1]
WaitTime = [A1+(1/1440)]

Application.OnTime RunTime, "test"

End Sub

Will this now work everyday without fail, even if nobody makes any changes to the sheet? It works fine when I test it, but obviously haven't been able to test it over a whole day?

 

[Harvey]

this line of code should be put in your Workbook_Open function:

Application.OnTime TimeValue("17:00:00"), "DoRefresh"

this function should be put in your workbook functions, ie. right under the Workbook_Open function:

public function DoRefresh()
  call MyMacro
  Wait(TimeValue(TimeValue("17:00:00") + TimeValue("0:01:00")))
  Application.OnTime TimeValue("17:00:00"), "DoRefresh"
end function

if you want to use the value from cell A1 as time value, replace the "17:00:00" with [A1] in the code lines.

 

[cob98tp]

Last time (I hope!)

I've put everything in exactly as you suggest but I get the message...


The macro "path\Book1.xls'!DoRefresh'Cannot be found.

what is going wrong?!?

When I try to put this in a seperate module it does not recognise the 'wait' function... Sorry to keep going on about this problem!

 

[Harvey]

if you put it in a module, you can use:

Application.Wait (..)

instead of 'Wait' (...)

I just figured that out, too
Don't know why the ontime won't run from the workbook, but excel has a lot of strange things..