Fire a macro

[Doug]

Any idea how to have fetch file automatically fetch the data at say; 7:30 AM
every weekday? Right now I have to push a button but may not always be here
throughout the day to do it and I would hate to miss a day’s worth of data or
more if I happen to be on vacation.
I normally press a button in cell A1 of the sheet to activate this macro.

 

[Mike H]

Doug,

You could do that with application.ontime privideing the workbook was open
of course.

This bit of code to get things started
Private Sub Workbook_Open()
Application.OnTime TimeValue("07:30:00"), "YourMacro"
End Sub

then this in a 'general Module' to make it continue to work every weekday at
07:30

Sub YourMacro()
Application.OnTime
TimeValue ("07:30:00"), "MyMacro"
If Weekday(Now, 2) > 5 Then Exit Sub
'YOUR CODE
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Doug]

Is there a way to do it with the workbook closed? I would rather not even
open the workbook unless I find that there is something wrong with the data
that I pull from it in another workbook.

 

[Gord Dibben]

Use Task Scheduler to start Excel and open the workbook every day at a
designated time.

You will have workbook_open code which fetches the data then saves and
closes the workbook then shuts down Excel.

Like 4:30AM when no one will be around and using Excel.


Gord Dibben MS Excel MVP

 

[Doug]

Once I set it like this, how can I stop the macro if I need to look at the
workbook?

 

[Doug]

This code doesn't close the workbook after it runs the application. Do you
know what I should have in there instead?

Application.Run "KingWebFetchSAS.xls!GetDataSAS"
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose

 

[Doug]

Thank you!
This code doesn't close the workbook after it runs the application. Do you
know what I should have in written instead to close the workbook?
Application.Run "KingWebFetchSAS.xls!GetDataSAS"
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose