Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.OnTime driving me crazy
Lars-ke & Tom
Sorted, working perfectly today. Many thanks. Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Driving me CRAZY~ please help | New Users to Excel | |||
VLOOKUP driving me crazy | Excel Discussion (Misc queries) | |||
HELP!? Passwords are driving me crazy. | Excel Worksheet Functions | |||
Application.OnTime driving me crazy | Excel Programming | |||
Driving me crazy! | Excel Programming |