OnTime VB

[AKorsakova]

I want to refresh my data every day at 12am. Does something like this
make sence?

Public RunWhen As Double

Sub StartTimer()

RunWhen = Time() = #12:00:00 AM# + TimeSerial(24, 0, 0)
Application.OnTime earliesttime:=RunWhen, procedure:="The_Sub", _
schedule:=True
End Sub

Sub The_Sub()


ActiveWorkbook.RefreshAll
StartTimer

End Sub

 

[Ardus Petus]

Sub StartTimer()
Dim RunWhen As Double
RunWhen = Time() = #12:00:00 AM# + TimeSerial(24, 0, 0)
Application.OnTime earliesttime:=RunWhen, procedure:="The_Sub", _
schedule:=True
End Sub

HTH

 

[Dave Peterson]

This looks funny to me:

RunWhen = Time() = #12:00:00 AM# + TimeSerial(24, 0, 0)

Maybe:

RunWhen = date + TimeSerial(23, 59, 59)
or
RunWhen = date + 1

Chip Pearson has lots of instructions at:
http://www.cpearson.com/excel/ontime.htm

 

[AKorsakova]

now with something like RunWhen = date + TimeSerial(23, 59, 59)
would that automatically refresh every day- or would it just do that
one day?

 

[Dave Peterson]

It would only do it once.

But you could have your code set up the next time right after that time
finishes.

Chip shows you how at that link.

 

[Chip Pearson]

It would run only once. See www.cpearson.com/excel/ontime.htm

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com