Application.OnTime driving me crazy
 

change
TimeValue("TimeCell2"),

to

TimeValue(TimeCell2),

--
Regards,
Tom Ogilvy

"Ron" wrote in message
10.201...
Hello vba gurus [green with envy, but learning]


I have a web query that needs refreshing periodically throughout the
day. Sometimes upto 20 times.
The web query URL is mostly static but has a 7 digit event number tagged
on the end.

i.e. http://webquery-site/market.asp?id=2211993

The url is static but the 7 digit number on the end changes every day,
and also several times through the day.

Ordinarily I do this manually but am going away for 2 weeks and want to
automate this so a friend can set it running on a morning and let it do
it's thing all day.

I have in column F a list of times ie 20:28:00 and in column H I have
the corresponding event id's ie 2211993, 2211994, 2211897 etc.

What I'm struggling with is getting the Application.OnTime procedure to
work correctly.

My code at the moment is..

Sub OnTimeTest()
Dim TimeCell2 As String
TimeCell2 = Sheets("test").Range("F2")
Application.OnTime TimeValue("TimeCell2"), "testProcedure"
End Sub


The variable TimeCell2 is the time that sits in cell F2.

The procedure "testProcedure" simply pops up a message box for now until
I get this right then I will link it to the sub that refreshes the
query.

I'm sure the solution is something simple but I'm at a loss now. I
think I'm going wrong on the syntax somewhere.

Any ideas guys?

Thanks

Ron

Application.OnTime driving me crazy
 

On Sat, 30 Oct 2004 19:27:32 -0400, "Tom Ogilvy"
wrote:

change
TimeValue("TimeCell2"),

to

TimeValue(TimeCell2),


That works fine if the cell contains e.g. 2004-10-30 01:02:03 but
if the date part is omitted, as in the example of the OP there is a
type mismatch which can be prevented by using the Format function.
I don't really understand why, but maybe you can explain the
difference Tom.

Lars-Åke

Application.OnTime driving me crazy
 

Cheers guys, however I'm having problems placing the - Format(TimeCell2,
"hh:mm:ss") - code.

Where exactly does this go, and does it replace part of the original?

I've tried it in various locations and still coming up with errors.

I'm going to try to put the whole date into the TimeCell2 and run the code
with Toms suggestion.

I'll also soldier on and try to figure the Format(TimeCell2) part as well.

Thanks,

Ron

Application.OnTime driving me crazy
 

On 31 Oct 2004 10:10:15 GMT, Ron
wrote:

Cheers guys, however I'm having problems placing the - Format(TimeCell2,
"hh:mm:ss") - code.

Where exactly does this go, and does it replace part of the original?

As I wrote in my response yesterday:

Replace the "TimeCell2" argument to TimeValue with

Format(TimeCell2, "hh:mm:ss")

That means that your code will end up like this

Sub OnTimeTest()
Dim TimeCell2 As String
TimeCell2 = Sheets("test").Range("F2")
Application.OnTime TimeValue(Format(TimeCell2, "hh:mm:ss")),
"testProcedure"
End Sub

and if you put the complete date and time in your F column cells, like
2004-10-31 14:47:00
then you can forget about the Format function.
Note that there should not be any " in the cell, just as shown above.


I've tried it in various locations and still coming up with errors.

Which locations have you tried?
Which errors do you get?

Lars-Åke


I'm going to try to put the whole date into the TimeCell2 and run the code
with Toms suggestion.

I'll also soldier on and try to figure the Format(TimeCell2) part as well.

Thanks,

Ron

Application.OnTime driving me crazy
 

Thanks Guys,

Had a mess around and now got the timed procedure working, and taking
the time from the correct cell, thanks a bunch.

I've now hit the hurdle whereas I can't get it to run more than once.

I've tried an If Then then statement increasing a counter by one to
control the number of runs of the code.

I've also tried a Do until loop but it doesnt seem to want to do it
twice.

Here's the code as it is now, working once but not twice.

Sub Start()
Dim counter As Integer
counter = 0
Dim countTotal As Integer
countTotal = Sheets("test").Range("i2")
Do Until counter = countTotal
OnTimeTest
Exit Do
counter = counter + 1
Loop
End Sub

Sub OnTimeTest()
Dim MyTime
Dim TimeCell2
TimeCell2 = Sheets("test").Range("k2")
MyTime = Format(TimeCell2, "hh:mm:ss")
Application.OnTime TimeValue(MyTime), "testProcedure"
End Sub

Sub testProcedure()
Sheets("test").Select
Range("K2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub


I've tried the ,Schedule:= False on the end on the App.OnTime line but
to no avail.

Any thoughts?

Regards,

Ron

Application.OnTime driving me crazy
 

Sub Tester1()
Range("D19").Select
ActiveSheet.Calculate
Application.OnTime ActiveCell.Value, "ABC"
MsgBox "done " & ActiveCell.Value
End Sub

Sub ABC()
MsgBox Format(ActiveCell.Value, "hh:mm:ss")
End Sub


Worked fine for me without using any format commands. the cell contained the
formula
=NOW()-INT(NOW())+TIME(0,0,5)

so it only contained a time.

--
Regards,
Tom Ogilvy

"Lars-Åke Aspelin" wrote in message
...
On Sat, 30 Oct 2004 19:27:32 -0400, "Tom Ogilvy"
wrote:

change
TimeValue("TimeCell2"),

to

TimeValue(TimeCell2),


That works fine if the cell contains e.g. 2004-10-30 01:02:03 but
if the date part is omitted, as in the example of the OP there is a
type mismatch which can be prevented by using the Format function.
I don't really understand why, but maybe you can explain the
difference Tom.

Lars-Åke

Application.OnTime driving me crazy
 

On 31 Oct 2004 13:56:58 GMT, Ron
wrote:

Thanks Guys,

Had a mess around and now got the timed procedure working, and taking
the time from the correct cell, thanks a bunch.

I've now hit the hurdle whereas I can't get it to run more than once.

I've tried an If Then then statement increasing a counter by one to
control the number of runs of the code.

I've also tried a Do until loop but it doesnt seem to want to do it
twice.

Here's the code as it is now, working once but not twice.

Sub Start()
Dim counter As Integer
counter = 0
Dim countTotal As Integer
countTotal = Sheets("test").Range("i2")
Do Until counter = countTotal
OnTimeTest
Exit Do
counter = counter + 1
Loop
End Sub

Lets assume that the Exit Do statement is put there just for testing
purpose (as it makes no sence to unconditionally exit the loop at that
point).
The result from this Start procedure would then be to have a
countTotal number of scheduled activations where the detailes are
left for the OnTimeTest to deal with. OK no problem so far.


Sub OnTimeTest()
Dim MyTime
Dim TimeCell2
TimeCell2 = Sheets("test").Range("k2")
MyTime = Format(TimeCell2, "hh:mm:ss")
Application.OnTime TimeValue(MyTime), "testProcedure"
End Sub

OK, now the problem starts. As this procedure is called a number of
times (by the Start loop) there will be a number of scheduled
activations of the testProcedure procedure.
However. All these activations will be on the same time, i.e. the
time found in cell K2 at the time of the execution of the above
mentioned loop. This must be different from what you really want.


Sub testProcedure()
Sheets("test").Select
Range("K2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub


OK, this procedure changes the value in K2 by shifting new values up.
But this is of absolutely no use since all the countTotal activations
are alreade scheduled to the time originally in K2.

So the effect of all this is just that (assuming the Exit Do is
removed) you will have a number of activations of the testProcedure
procedure at the (almost) same time, each shifting the K column
up one step.


I've tried the ,Schedule:= False on the end on the App.OnTime line but
to no avail.

Any thoughts?


Yes, try modifying the Start procedure to call Application.OnTime
with the DIFFERENT wanted activation times. These times could,
according to your original specification (although the F column seems
to be the K column now), be found in the K column.
The OnTimeTest you can skip and the "testProcedure" should only
do what you would like to happen at the specified times which is
probably something else than modifying the K column.
You might also want to change the loop exit criteria (from = to =)
if you would like to use the value 0 with expected result (i.e. no
activations).

With the above taken into account and without changing anything else
your code would the be like this:

Sub Start()
Dim counter As Integer
counter = 0
Dim countTotal As Integer
countTotal = Sheets("test").Range("i2")
Do Until counter = countTotal
Application.OnTime
TimeValue(Format(Sheets("test").Range("K2").Offset (counter, 0),
"hh:mm:ss")), "testProcedure"
counter = counter + 1
Loop
End Sub

Sub testProcedure()
MsgBox Format(Now() - Int(Now()), "hh:mm:ss")
' replace this with what you really want to do here
End Sub

Lars-Åke

Regards,

Ron

Application.OnTime driving me crazy
 

On Sun, 31 Oct 2004 11:47:20 -0500, "Tom Ogilvy"
wrote:

Sub Tester1()
Range("D19").Select
ActiveSheet.Calculate
Application.OnTime ActiveCell.Value, "ABC"
MsgBox "done " & ActiveCell.Value
End Sub

Sub ABC()
MsgBox Format(ActiveCell.Value, "hh:mm:ss")
End Sub


Worked fine for me without using any format commands. the cell contained the
formula
=NOW()-INT(NOW())+TIME(0,0,5)

so it only contained a time.

It seems to have something with the actual formatting of the cell.
If the format is a "date" format (even with month name as text etc)
it works without calling the Format function, but if the cell format
is General or even Time (without the date part), then I will get the
Type mismatch when the cell value is given to the OnTime procedure.
So it is a bit strange that using Format with "hh:mm:ss" as a
parameter does note yield type mismatch as a cell format
of "hh:mm:ss" does.
All of this is experienced with Excel 2002.

Lars-Åke

Application.OnTime driving me crazy
 

Mine was formatted as time as shown by the OP in the original post.

--
Regards,
Tom Ogilvy

"Lars-Åke Aspelin" wrote in message
...
On Sun, 31 Oct 2004 11:47:20 -0500, "Tom Ogilvy"
wrote:

Sub Tester1()
Range("D19").Select
ActiveSheet.Calculate
Application.OnTime ActiveCell.Value, "ABC"
MsgBox "done " & ActiveCell.Value
End Sub

Sub ABC()
MsgBox Format(ActiveCell.Value, "hh:mm:ss")
End Sub


Worked fine for me without using any format commands. the cell contained
the
formula
=NOW()-INT(NOW())+TIME(0,0,5)

so it only contained a time.

It seems to have something with the actual formatting of the cell.
If the format is a "date" format (even with month name as text etc)
it works without calling the Format function, but if the cell format
is General or even Time (without the date part), then I will get the
Type mismatch when the cell value is given to the OnTime procedure.
So it is a bit strange that using Format with "hh:mm:ss" as a
parameter does note yield type mismatch as a cell format
of "hh:mm:ss" does.
All of this is experienced with Excel 2002.

Lars-Åke

Application.OnTime driving me crazy
 

Formatted it as general. Worked fine for me.

xl97.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Mine was formatted as time as shown by the OP in the original post.

--
Regards,
Tom Ogilvy

"Lars-Åke Aspelin" wrote in message
...
On Sun, 31 Oct 2004 11:47:20 -0500, "Tom Ogilvy"
wrote:

Sub Tester1()
Range("D19").Select
ActiveSheet.Calculate
Application.OnTime ActiveCell.Value, "ABC"
MsgBox "done " & ActiveCell.Value
End Sub

Sub ABC()
MsgBox Format(ActiveCell.Value, "hh:mm:ss")
End Sub


Worked fine for me without using any format commands. the cell
contained
the
formula
=NOW()-INT(NOW())+TIME(0,0,5)

so it only contained a time.

It seems to have something with the actual formatting of the cell.
If the format is a "date" format (even with month name as text etc)
it works without calling the Format function, but if the cell format
is General or even Time (without the date part), then I will get the
Type mismatch when the cell value is given to the OnTime procedure.
So it is a bit strange that using Format with "hh:mm:ss" as a
parameter does note yield type mismatch as a cell format
of "hh:mm:ss" does.
All of this is experienced with Excel 2002.

Lars-Åke

Application.OnTime driving me crazy
 

On Sun, 31 Oct 2004 15:15:22 -0500, "Tom Ogilvy"
wrote:

Formatted it as general. Worked fine for me.

xl97.

It is the TimeValue function, not the OnTime method, that
seems sensitive to the cell formatting.
Still a bit strange.

Lars-Åke

Application.OnTime driving me crazy
 

Lars-Åke

I wasn't aware that the OnTime procedure actually queued all the requests
up at once, and then ran the requested code at the given time.

I actually thought it would run once at the time, then wait for the next
time and so on.

Everything working perfectly now.

Can't thank you & Tom enough.

Regards,

Ron

Application.OnTime driving me crazy
 

Lars-�ke & Tom


Sorted, working perfectly today.

Many thanks.

Ron