Excel Pivot Table Question:
I would like to understand how I can schedule a pivot table to refresh
once a day, either through VB or through purchasing a job scheduler
that will refresh excel pivot table report automatically. The pivot
table shouldn't be set up to refresh automatically on any rotation
other than once per 24 hours. The pivot table should gather it's
external data through a database using ODBC.

Hi curtisimodigeo,

Nothing tricky needed (except the process of actually finding this
information starting from Microsoft's help files, which give the wrong
answer to this question in the case of pivot tables).

Set up your pivot table, then right-click it and select "Table
Options..." from the pop-up menu. At the bottom of this is an checkbox
for "Refresh every ___ minutes". Check this and enter 1440 as the
number of minutes, and all should be well.

Cheers, manuka-ga

I will test this right now. Thank you, and I'll confirm this actually
working by Weds. I was aware of this function in pivot tables, and
didn't think it worked. but I may have been mistaken.

The option doesn't appear to work. Your answer only works if the file
is already open. I need a solution that will open up pivot tables,
refresh them, and close them, on a dayly basis.

Hi curtisimodigeo,

If you mean that the database source file is closed, then I don't
experience that problem. If you mean that the Excel file is closed
then that's a whole different matter - and it's something that you
neglected to mention in your initial query.

The best solution is probably to create a batch file to open an Excel
spreadsheet which automatically opens the files, refreshes the
PivotTables, closes the files and then closes itself (and Excel, if
there are no other open files). You can then use the Windows Task
Scheduler to run the batch file automatically every day. This is
probably the best way to go; other methods would rely on Excel being
open.

I don't know how much of this you need help with. Here are a few tips:
 - Your batch file just needs to call Excel with the name of the
workbook to open (this will not be a PivotTable workbook but the
control workbook). Under modern versions of Windows with the normal
settings it can probably just have the workbook details.
 - For the control workbook, put code like this in the Workbook_Open() macro:
RefreshWorkbook("C:\path1\filename1.xls")
RefreshWorkbook("C:\path2\filename2.xls")
RefreshWorkbook("C:\path3\filename3.xls")
RefreshWorkbook("C:\path4\filename4.xls")
RefreshWorkbook("C:\path5\filename5.xls")
...
where obviously the strings should be replaced with the correct drive,
path and filenames. At the end of this sub, check to see how many
files Excel has open, and if it's only the current file, close Excel,
otherwise just close the workbook (ThisWorkbook.Close)

- Then add a sub called, you guessed it, RefreshWorkbook, with a
string parameter. This sub needs to open the file given by the
parameter, refresh any PivotTables in the file, and then close the
file. I assume you can handle the open and close parts yourself (if
not, please say so). Assuming you've opened it and assigned it to a
Workbook object variable called CurrentFile, the code to refresh the
PivotTables would look like this:

Dim CurrentFile As Workbook, Sheet As Worksheet, PTable As PivotTable
... ' open file, assign to CurrentFile
  For Each Sheet In CurrentFile.Worksheets
    For Each PTable In Sheet.PivotTables
      PTable.RefreshTable
    Next PTable
  Next Sheet
... ' close file

Note that RefreshTable returns True if successful and False otherwise,
so you could add failure logging to this if you want.

Anyway, hopefully that will be enough to get you started. If you need
more help, let me know.

Actually, we can skip one step in my previous comment and just use the
Windows Task Scheduler to run the control spreadsheet directly.

Also, if you want to be fancy you can store the filenames in the
spreadsheet part rather than the code. It's not going to make a huge
amount of difference. In either case the tricky part is going to be
maintaining the list - you'll have to prevent the automatic macro from
running by holding SHIFT down while opening the file. One way around
this would be to store the filenames in a separate file. (Text would
be enough, doesn't need to be an Excel file. For a text file, read the
names with the Input # statement.)