OnTime Method

[pd2004]

Hello,

I am interested in finding a method like the OnTime method, except that I want the method to interrupt the code that is running. The idea is to give the VBA code a limited time to either complete the task, or move on. OnTime only runs when the current procedure completes.

Thank you,

Pat

 

[SkipVought]

Hi,

Are you running a loop? set a timer to exit the loop.

dim t, p
t = timer
p = 5 '5 seconds
do
' yer stuff
  if t + p < timer then exit do
loop

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

[tt]Application.Wait (Now() + TimeValue("00:00.03"))[/tt]

is the easiest.

If you would rather make it wait until something is done... that's a different story, but it doesn't look like you would!

 

[pd2004]

Hi Skip,

Thanks for looking at this. I appreciate it. The problem is that if the code hangs while doing "yer stuff" above, the loop is never evaluated. This is happening to me when I try to Querytable.Add in my Excel VBA. If the connection to the web hangs up, for example, Excel will wait indefinitely. I need it to move on...

I have looked and looked. Thanks for your help, Skip!

Pat

 

[Gruuuu]

Oh, I misunderstood. Wait won't do what you want.

You could Mix Skip's stuff with a timed thingy though.
[tt]
myTime = Now() + TimeValue("00:00:03")

For i = 1 to 5
'stuff
If Time > myTime Then Exit For
Next i
[/tt]

 

[pd2004]

Hi Gruuuu,

Thanks for your input and help. Again, though, I thing that if the hang up takes place during the "'stuff" above, the next line of your code is not evaluated, and Excel will hang indefinitely. I am not a professional programmer, so let me know if I am really missing it here.

Thank you again!

Pat

 

[SkipVought]

what is 'yer stuff' code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Nope, you're right, that's a problem.

I haven't tested this, but give this a shot:

[tt]
myTime = Now() + TimeValue("00:00:10")

myQueryTable.Refresh (True) 'this is the background query flag

Do
While myQueryTable.Refreshing = True And Time < myTime

If myQueryTable.Refreshing = True Then myQueryTable.CancelRefresh
[/tt]

 

[SkipVought]

if you're using the code in your other post, you have a HUGE problem!

In the loop you are ADDING a querytable each time you loop.

WHY?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pd2004]

Hi Skip,

The idea was that if it loops once successfully, it exits the loop and moves on. If it hangs up in the loop for longer than the time allotted, then it exits the loop too. Either way, I don't think it works because it doesn't do anything until the querytable refreshes (as long as that may be).

Anyway, I appreciate all of your help.

Pat

 

[SkipVought]

If it loops at all, you KEEP ADDING QUERYTABLES!!!!!!

WHY?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pd2004]

Skip,

It was probably a backwards way of thinking of things, but I am pretty sure it would exit the loop for line 200 if the querytable refreshes successfully. Regardless, it doesn't do what I want (to exit if it hangs indefinitely), because nothing is evaluated until the querytable adds/refreshes.

Back to my point, are you aware of a way to force the code to abort/goto a line number if a given process takes longer than we might like? If so, please let me know.

Thank you,

Pat

 

[SkipVought]

when I run this code it executes in SECONDS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

pat, the code I saw on the other thread was a QueryTable.Add... I'm not terribly certain if that has much to do with the way it's working, BUT
If you have a query already defined, you can change the connection and have it refresh (if it's going to change).

If instead you're making a new file and adding the querytable, I suggest making yourself a template.
Then try my code and see if it works.

 

[SkipVought]

That is the way I would do it. Then it is merely...

Activesheet.QueryTables(1).Refresh False

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pd2004]

I really want to thank you guys. I appreciate all of your thoughts and direction. I think the template idea might be more stable. I will work on that.

Thank you,

Pat