Solved: Macro to AutoRun at Night on Network - will it work?

[SherryO]

I have a macro that refreshes 12 pivot tables during the night. I have it set to 4AM EDT right now. My questions are:

1.) If I have it stored on a network drive, what will happen? Will it run?
2.) If I don't specify a time for it to end, will it run everynight?
3.) Do I have to run it from a specific machine?
4.) Do I have to have the file which contains the code open?

Any help would be appreciated.
Thanks,
SherryO

[VBA]
Application.OnTime EarliestTime:=TimeValue("17:00:00")

Workbooks.Open Filename:="C:\_sjr\ProjectAnalysis\ProRepPTs.xls"
Workbooks("proreppts.xls").Activate
Sheets("Stat").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("ActCost").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Workbooks("ProRepPTs.xls").Close xlSaveChanges = 1
[/VBA]

Edited 10-Apr-07 by geekgirlau. Reason: insert vba tags

[mdmackillop]

If I was to to this, I'd probably use Windows Scheduled Tasks to open an Excel file with a Workbook open event to run the code.

[SherryO]

What is Windows Scheduled Tasks? I've never heard of it. Thanks

[Bob Phillips]

Click the start button, click on Help, and lookup 'Schedule a new task'.

[mdmackillop]

Start/All Programs/Accessories/SystemTools/Scheduled Tasks

[SherryO]

Very cool. Does this have to run from one given machine? I work remotely, so I would have to find a machine to run it. thank you so much.

[mdmackillop]

I think this will have to be run from 1 machine. I've never got involved in remote working, so don't know of other possibilities.

[Charlize]

When using terminal server sessions ... you can log in via the remote machine and in this session, make a task with the taskscheduler. Disadvantage is that you may not log off of your session but click the x in the middle of your screen. Your session will rest active on the server and the task will be performed. This will only work if the session period can be unlimited. If company polices are that a session can be active for about 4 hours (or less) max and than shuts down automatically, this won't help you alot.

I, for example must remember that I logged in from my homecomputer or I can't log in via the head server before I close down my remote session. In other words, I have to terminate the remote session before I attempt to login at the normal way (sitting at my desk at work).

Charlize

[Ken Puls]

I'm curious as to your setup. Any way you slice this, it will need to be run on A machine. The versatility depends on how you set it up.

If you do use Windows scheduled tasks, you're stuck to the machine you set up the task on. If you use the OnTime method in the workbook, you can use it on any machine, but you'll have to leave the Excel session open overnight.

Either way, you need a PC left on overnight to run the task.

Charlize's question is interesting though... if I needed to do this, I would set up the task on the server console of my terminal server. It's always logged in, so would always run overnight without me having to leave another machine going.

[SherryO]

Thank you for the advise. I'll talk to my network guys and see if I can work it out. SherryO