Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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 -- HTH... Jim Thomlinson "Andyjim" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Andyjim" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Schedule downloads in VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Template downloads | New Users to Excel | |||
Schedule downloads | Excel Programming | |||
Schedule downloads | Excel Programming | |||
Schedule Downloads | Excel Programming | |||
Macro to schedule downloads | Excel Programming |