Schedule A Macro To Run With Scheduled Tasks

[RoyinUK2000]

I have a worksheet that runs a query from an external data program. I have made a macro that refreshes the worksheet and this works ok. Is there a way I can get Scheduled tasks to automatically update this workbook eg overnight. I do not want to leave the workbook open either. Can task scheduler open the workbook, refresh the data, then close the workbook again.


Please help

Thanks

Roy

 

[RoyinUK2000]

Come on guys please I need a quick response to this one

 

[s-o-s]

Yes,

Add the excel file to the task scheduler.

Add an Autoopen macro to your excel book

Add in lines to save the book and then close the application into the macro.

The quit code is as follows.

application.quit

To add the file to scheduled tasks select add a task...Then when the program screen pops up click browse and just navigate to your Excel file...you dont have to start Excel your PC will do that automatically.

 

[Legacy 98055]

This should work for you...
Place in your Workbook Class Code Module<pre>Private Sub Workbook_Open()
TheNameOfYourCurrentProcedureHere
Me.Close True 'save the changes and close the workbook
End Sub

If you would like to limit this procedure to only run when
opened by the scheduler, then test the time. Fo ex. Say you
are running this procedure Sun - Fri at 0330 AM. Use code similiar
to the following...

Private Sub Workbook_Open()
If Hour(Now) = 3 And WeekDay(Now, vbSunday)< 7 Then
TheNameOfYourCurrentProcedureHere
Me.Close True 'save the changes and close the workbook
End If
End Sub</pre>Tom

We're not a vending machine or fast food!
_________________
Using Office 8, 9, & 10 on Windows Millenium
This message was edited by TsTom on 2003-01-28 07:28

 

[RoyinUK2000]

Is there a way to point task scheduler to a specific macro rather than have the sheet refreshing every time it is opened by using an auto-open macro that refreshes.


Please help.

Roy.

 

[Legacy 98055]

Cleaner code here which end Excel if this is the only workbook open at the time...<pre>Private Sub Workbook_Open()
If Hour(Now) = 3 And WeekDay(Now, vbSunday) < 7 Then
' TheNameOfYourCurrentProcedureHere
If Workbooks.Count = 1 Then
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub

</pre>om

 

[s-o-s]

On 2003-01-28 07:27, RoyinUK2000 wrote:
Is there a way to point task scheduler to a specific macro rather than have the sheet refreshing every time it is opened by using an auto-open macro that refreshes.


Please help.

Roy.

Not absolutely sure what you mean...you can not run a specific macro from one file.

However, you could open one file that contains an autoopen macro which then calls other files and runs macros in those...

Is this the sort of thing you mean...

 

[Legacy 98055]

He wants the sheet to automatically run code ONLY when it is opened byt the task scheduler. One method was listed. There might be other alternatives???

 

[john2kin]

He wants the sheet to automatically run code ONLY when it is opened byt the task scheduler. One method was listed. There might be other alternatives???

yes there are but i am not sure and still in search..if u find do tell me

 

[WardMD]

While the above suggestions are quite good, below is a slight variation which does NOT require ensuring the Scheduled Task run at a specific time (or that the Macro and the Scheduled Task be in "agreement" as to WHEN the automation happens):

Private Declare Function GetActiveWindow Lib "user32.dll" () As Long

Private Sub Workbook_Open()
'
' Macro runs when workbook opens
' Runs when Spreadsheet (worksheet) is OPENED. Allows running Excel from Task Scheduler (Update/Save/Quit).
'
Call Application.OnTime(VBA.Now, "ThisWorkbook.TestInteractive")
End Sub

Private Sub TestInteractive()
If GetActiveWindow() = 0 Then
' We're running via the Task Scheduler: Update the Spreadsheet/Graph
If Workbooks.Count = 1 Then
' YourMacroNameHere
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub