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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top