Running a macro that samples a website all day long

feadrian

New Member
Joined
Oct 17, 2015
Messages
3
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,747
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:
Code:
Set ScheduledTimeCell = ScheduledTimeCell.Offset(0, 1)
 

feadrian

New Member
Joined
Oct 17, 2015
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,314
Messages
5,635,506
Members
416,861
Latest member
Breadnjam18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top