Running a macro that samples a website all day long

[feadrian]

Hello:

Suppose in an excel Row I have a series of time values (hours of a day). For example, Row 4 has the following series along columns:
7:00:00; 8:10:00; 10:30:00; 15:23:00
The mission of this macro is:
- Check the first timestamp
- Schedule a web sample (scraping) at this time
- Do the sample when this time is reached
- Copy and paste values sampled in another page (already solved)
- Get next timestamp

This macro should start when a button is pressed and finished after current time is greater than the rightmost timestamp.

My only concern is how to schedule this sampling at that given time.
I've tried something like this inside a loop:

Application.OnTime NextSampleTime, "Sampling Sub"

I guessed that this code, when invoked, remained in standby until current time reached the timestamp and then sampled and continued, but it doesn't do such thing. It doesn't wait until next moment

Do you know any way of doing this?

Some conditions I need to meet:
- The sampling should be handled by excel (the macro itself) and not by some windows task scheduler that opens and closes excel each time
- Only one time at any given time of day should someone press the Start button, and sampling should go on until last timestamp
- The processor use should be minimal (i.e. There's got to be that's not checking the time on each millisecond, but rather scheduling tasks ahead)

Thanks in advance!

 

[John_w]

My only concern is how to schedule this sampling at that given time.
I've tried something like this inside a loop:

Application.OnTime NextSampleTime, "Sampling Sub"

I guessed that this code, when invoked, remained in standby until current time reached the timestamp and then sampled and continued, but it doesn't do such thing. It doesn't wait until next moment

On the face of it, that code should schedule "Sampling Sub" to run at NextSampleTime today, as long as the time has not already passed, in which case "Sampling Sub" will run at that time the next day, as long as the workbook has not been closed or Excel is still open. Post the full code if you need help with it.

Pearson Software Consulting shows how to code an Application.OnTime timer. For your situation, you would probably have a Range variable which references the cell containing the next scheduled time, instead of the RunWhen variable. Get the next cell in the row with something like:

Set ScheduledTimeCell = ScheduledTimeCell.Offset(0, 1)

 

[feadrian]

I'm reading it right now, Thanks

Does anyone know if there's a way to send parameters to the Procedure that will be scheduled?
I mean, something like

Application.OnTime NextSampleTime, "Sampling Sub (Argument1, Argument2)"

If this is possible -because I'm getting an error- I need these arguments to be different instances of a variable (in orden not to overwrite them)Thanks again

 

[John_w]

See The Code Net and Syntax for Application.Ontime incl Variable Parameter | PC Review

 

[feadrian]

Cool!
It took a couple of extra hours, but it's working now