Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables (2002)

    Hello
    Can anyone help with an efficient way to populate pivot tables?
    My users require a report which consists of 40 + pivot tables on a worksheet. They all use the same recordset from an Access database (using a System DSN data source). Currently whenever they need one of these reports, they build it from scratch by creating a pivot table and copying and pasting it several times, then selecting the fields, page fields etc.
    I have created a template with all the required pivot tables on it, which presents a selection criteria form to the user, then populates the tables from the database. The problem of course is that the database query gets hit 40 + times which is very inefficient. I guess I could read the records onto another worksheet and use that to fill the tables but that seems like an ugly solution to me. I was hoping I might be able to set a recordset variable and use that, but I don't know how to.
    Any suggestions?
    Thank you.
    By the way, my Excel programming skills are not real strong, being more of an Access programmer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables (2002)

    Once you have created a first pivot table, you can base the subsequent ones on the first one; this will make them share the same data storage.

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (2002)

    Thats great, it is clearly easier than I thought. Does this mean that, provided all pivot tables are copied and pasted from the first, that programatically I could refresh the first one with a command such as
    Worksheets("Sheet1").PivotTables("PivotTable1").Pi votCache.Refresh
    and the others would also be refreshed?
    Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables (2002)

    A little test seems to confirm this: refreshing the first pivot table automatically updates subsequent pivot tables based on the first one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •