Schedule downloads in VBA

[Andyjim]

Hi,

I have a macro, "GetQuotes", that downloads market data. I need to run the
Get Quotes macro every 10 minutes from 6PM Sun to 5PM Fri. RBS gave the
following reply:
Simplest is to use the Windows task scheduler.
This can start Excel with a workbook as an argument to open that workbook.
Code can then be started from the Workbook_Open event and your code can then
detect the end of Friday to stop running and close the workbook
and then do an Application.Quit.

Does that mean have Windows Task Scheduler start Excel every 10 minutes, and
it will open a certain workbook and launch the macro each time an instance
of Excel is launched?
I'd much rather run the schedule in VBA if that is possible. Maybe there's no
capability in VBA for timing events in this way?

Thanks,
Andy

 

[Jim Thomlinson]

Probably the best way is to use the scheduler to open the workbook on Friday
at 5PM. The workbook will stay open all weekend. In the on open event of the
workbook create a call to a recursive procedure that uses the on time event
to relaunch itself. Here is the pseudo code...

Sub Workbook_OnOpen()

Call GetStuff
end sub

sub GetStuff()
if day(now) = Sunday And Hour(now) >= 5Pm then
application.quit
else
Retrieve your data
application.ontime Now + timerserial(0,10,0), "GetStuff"
end sub

 

[Jim Thomlinson]

Sorry I should have mentioned that that is just pseudo code and it will not
compile or run. It also does not deal with saving the workbook. I would be
inclined to save as part of the data retrieve but that is up to you.

 

[Andyjim]

Thanks Jim,
The following is working:
*********
Sub Workbook_OnOpen()

Call Scheduler

End Sub
*********
Sub Scheduler()
Dim TheTime
TheTime = Time

'IF day(now) = Friday And Hour(now) >= 5Pm then
If TheTime = #5:00:00 PM# Then
Application.Quit
Else
GetQuotes 'Call GetQuotes macro
Application.OnTime Now + TimeSerial(0, 10, 0), "Scheduler" 'Wait 10m
End If
End Sub
**********

But I don't know how to get Friday into the mix. Good idea, to Save after
each download. I'll do that.

Andy

 

[Andyjim]

Got it. This works:

Sub Scheduler()
Dim TheDate
Dim TheDay
Dim TheTime
TheDate = Date
TheDay = Weekday(Date)
TheTime = Time

If TheDay = 6 And TheTime >= #5:00:00 PM# Then 'If Friday, 5PM
Exit Sub 'Quit getting data
Else
GetQuotes 'Call GetQuotes macro
ActiveWorkbook.Save
Application.OnTime Now + TimeSerial(0, 10, 0), "Scheduler" 'Wait 10
min
End If

End Sub

 

[Andyjim]

Shucks, another problem. If another workbook is active when the scheduler
runs GetQuotes, it attempts to run it in the active workbook. I could have it
reactivate the pertinent file, but is there a way to have the scheduled macro
activity running in the background, so it does not interrupt the user working
in another workbook?

Andy