VBA to update Pivot table

Hello,

Can someone provide the code I can use to automatically update my pivot
tables upon opening the workbook.

Thanks

Why use code? Pivot tables have an option to refresh on open under the table
options if I recall correctly.
--
HTH...

Jim Thomlinson


"JR" wrote:

> Hello,
>
> Can someone provide the code I can use to automatically update my pivot
> tables upon opening the workbook.
>
> Thanks
>

Yes, Jim was right.

You just need to right-click the pivot table, choose "Table Options". Check
the "Refresh On Open" checkbox. (And save you file.)

In case you really want to know the statement for updating a Pivot table, it
will look like this:
ActiveSheet.PivotTables(1).PivotCache.Refresh

Regards,
Edwin Tam
[email protected]
http://www.vonixx.com


"Jim Thomlinson" wrote:

> Why use code? Pivot tables have an option to refresh on open under the table
> options if I recall correctly.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "JR" wrote:
>
> > Hello,
> >
> > Can someone provide the code I can use to automatically update my pivot
> > tables upon opening the workbook.
> >
> > Thanks
> >

excel 9 (2000) and VBA 6 on XP

If I open a workbook with pivot tables linked to database and set for 'refresh on open'; i get a 'query refresh' dialog asking if i want to update. Fine, i can click yes, update. The pivot tables then update.

However.... If I use code to open the same workbook, no dialog appears, and no automatic query / pivot table updates take place.

my question is:

can i force the pivot tables in a workbook to refresh when opened using code?

I guess i will have to do the following:

open workbook
loop through worksheets
loop through all pivot tales on worksheet
refresh pivot table
next
next
close workbook

andy other ideas on a quick way to force all to be updated? Afterall, you can refresh data from any linked FILES to on file open...

any good ideas folks?????

andy