Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
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 "Cam" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
Ooops, fat fingers...
Falseue should be False In both instances... Sorry. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Cam" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
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/ex...580301033.aspx HTH, Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
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 "egun" wrote in message ... 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/ex...580301033.aspx HTH, Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh data once a day?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable auto-refresh with external data | Excel Worksheet Functions | |||
Auto-refresh external data? | Excel Discussion (Misc queries) | |||
My graphs no longer refresh w/new data, why? auto calc is on. | Charts and Charting in Excel | |||
How can I auto-refresh auto-filters when data changes? | Excel Worksheet Functions | |||
How Do I Automatically Refresh Auto Filtered Data? | Excel Discussion (Misc queries) |