Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Cache (Office97 SR2 & Office 2000)

    Hello All:
    Is there any way to delete a Pivot Table Cache (in Excel or using VBA) short of deleting the Pivot Table?
    Here is The problem:
    When you Refresh the cache or refresh the Table using a new data source, the table retains all the old fields even when the new fields are entirely different. So, suppose that you want to set up an application that gives the user the option of switching Data Sources, where the fieldnames differ markedly between the Sources (like for different years where the field names have changed). You run into a problem, If you want to let the user select fields to include/exclude (by clicking the down arrows besides the Fieldnames), because the user will see ALL the fields ever accessed by the Pivot Table (many of which he/she may have never seen before). This erodes the user's confidence in the application. It also makes the task of selecting/excluding fields more tedious.
    This problem has bugged me for a long time and I find it hard to believe that MS has not addressed it in the KB (anyway, I can't find it).
    Thanks for your thoughts on this.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Cache (Office97 SR2 & Office 2000)

    As far as I know you cannot change the properties of the PivotCache object in order to correct the path to the database files. The only alternative is to delete the pivot table and recreate it based on the new information.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Cache (Office97 SR2 & Office 2000)

    Hello Hans:
    Thanks for the reponse. Sorry I was not clearer but by "gives the user the option of switching Data Sources" I did not mean that I wanted the ability change the "path to the database files". The path would be the same but the data in the database would have changed. Or, the data brought back (by Query) to the worksheet range (that the PT is based on) would change. Another situation would be where I might want have the user use the same PT for different ranges: Range1, Range2, and Range3 in the same WkBk (where the ranges might have data representing different future scenarios in a decision process).
    What happens is that the more scenarios or ranges or reporting periods you use for the same PT, the longer becomes the list of fieldnames. For example, I developed an application that computes bi-monthly efficiency rates and production rates as a % of standard for 6 major product lines. Since there are moderate turnover rates and many occasioins when we have to ask Supervisorary staff to pitch, the Worker-Name field quickly gets infused with names of people who are no longer here or are not subject to the monitoring. So each month, it gets more time consuming to filter out the names you do not want to show up in the Rating Tables.
    Anyway, I did get one suggestion that sounded like it would work but I do not know how to implement it. The suggestion is:
    You can loop through the PivotItems Collection of the PivotField that may have "Ghost" pivotitems and check if the RecordCount = 0, if so Delete those PivotItems.
    Any help in how to implement this would be appreciated.
    Thanks
    Stephen

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Cache (Office97 SR2 & Office 2000)

    Th PivotCache object does not itself store the data but contains a description of the parameters that are necessary for reading in external data. I think it is not permitted to change the properties of the PivotCache object. I think that if you do this, some of the layout information for the pivot table is lost and this will result in an error. I don't know if there exists a 'fix-it code'. Maybe you can find some more information related to that problem in Michael Kofler's book 'Definitive Guide to Excel VBA'?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Cache (Office97 SR2 & Office 2000)

    HI Again:
    I am having a problem with Pivot Tables that I think may have something to do with the Cache size limitation. In my applications where the PTs are handling a lot of data, I commonly see that there are places in the PT where it has simply failed to calculate. For example, suppose that my row fields are months within a geographic range (e.g., East, West, North, South). What I see is like East, North, and South show data for each of the months but West may sit there showing no months. When this has happened, I have, at times, been able to get 'West' going by double clicking on it. But sometimes I have to refresh the PT a couple of times and double click it again. Its like I used to do with the Fuel Injector in my old MGA (reach around and smack it with a hammer).
    I can not do anything about upgrading my system (a 400 mghrtz pentium2 with 128 megs mem) or the XL version (97 SR2) because we are a govt contractor. So, is there anything else that I can do to avoid these problems? Like, can I increase the Cache size; or, could the problem be occuring because there are too many PTs in the same WS or WB?
    Would it help If I put them in different WSs or WBs, eventthough they are all run by the same VBA code?
    Anyone have some ideas?? <img src=/S/cauldron.gif border=0 alt=cauldron width=20 height=20>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Cache (Office97 SR2 & Office 2000)

    I don't know of any limitations, but, to give you an idea of what is possible, I give you an example of one of my applications. First, I am using a quite similar computer configuration as yours: 450MHz, 128MbRAM, Excel 2000 (but I don't know if that makes the difference): I one workbook, I have a database sheet containing over 150 columns and about 950 rows, and 4 sheets containing each about 100 pivottables, which were generated using a macro. All pivottables have count and percentage data fields, column and row fields; no page field. The complete file is about 8Mb. No problems!

Posting Permissions

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