Auto refresh data once a day?

[Cam]

Hello,

I have several pivot table in different worksheets that I would like to
automate it to refresh all tables everyday @ 6:30AM. I created a refresh All
macro, but don't know how to tell it to execute at the given time each day.
Any help is appreciated. Thanks

 

[Bernie Deitrick]

Option Explicit
Dim NextTime As Date

Sub ScheduleRun()
'Schedule for tomorrow at 6:30 AM
NextTime = Date + 1 + TimeValue("06:30:00")
Application.OnTime NextTime, "RunMe"
End Sub

Sub RunMe()
MsgBox "Hello"
'Run your PivotTableRefreshAll macro from here
'Schedule for the next day (optional)...
'You cannot shut down Excel for this to work
Application.OnTime NextTime + 1, "RunMe", schedule:=True
End Sub

Sub StopMe()
'Needed if you auto scheduled for tomorrow
MsgBox "Goodbye"
'Un-schedule for tommorrow...
Application.OnTime NextTime + 1, "RunMe", schedule:=Falseue
End Sub

Sub StopMeToday()
'Needed if you want to stop before 6:30AM
MsgBox "Goodbye"
'Un-schedule for today...
Application.OnTime NextTime, "RunMe", schedule:=Falseue
End Sub


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ooops, fat fingers...

Falseue

should be

False

In both instances...

Sorry.

HTH,
Bernie
MS Excel MVP

 

[egun]

This assumes that the workbook is open 24 hours a day, right? If not, then
this code won't run, as you acknowledged.

A possible solution to that is to schedule a task that opens the workbook at
a certain time each day (say 06:00) and have the Workbook_Open macro do the
pivot table refresh. To schedule the task, use the Scheduled Tasks uitility
under "start/All Program/Accessories/System Tools" (in XP, at least), and set
up a task that opens Excel and your workbook.

The command would be something like:

excel.exe "c:\My Folder\book1.xlsx"

You can have this happen whether or not you are logged in, as long as the
machine is turned on. You will probably have to supply your password to make
it work. One thing I'm not sure of - unless you set the security level low
enough, Excel always asks if you want to enable macros. That might get in
the way of automatically opening the file.

There are command line switches you can use to open Excel:

http://office.microsoft.com/en-us/excel/HA101580301033.aspx

HTH,

Eric

 

[Bernie Deitrick]

Just a clarification: the workbook with the macro does not need to be open, nor does the workbook
with the pivot table, but Excel would need to be running.

HTH,
Bernie
MS Excel MVP

 

[Spiggy Topes]

I use scheduled tasks for a number of daily processes. They get all
the routine stuff out of the way each day before I'm even awake. The
only time they don't work is when the box is shut down or I change my
password and forget to update the scheduled tasks. It's easier to set
up and much more flexible to schedule than an Excel-only solution. You
can set it to run weekdays only, every 30 minutes from 9:00 am to 4:30
pm, you can disable it with the click of a checkbox, lots of options.

I find it useful to use an Auto_Open macro in a separate workbook ("X:
\Wherever\Scheduled Run.xls"), so that I can maintain the real macro
without accidentally launching it each time I go in to tweak. This is
it:

Sub Auto_Open()
Workbooks.Open "X:\Wherever\Your Macros.xls"
Run "'Your Macros.xls'!Run_Me_Now"
Workbooks("Your Macros.xls").Close
Application.Quit
End Sub

The scheduled task's run command is simply "X:\Wherever\Scheduled
Run.xls"

Only thing you need to remember is, whenever your password changes
(assuming you have one), update the scheduled tasks too.