Clearing Pivot table fields "memory" in VB?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Clearing Pivot table fields "memory" in VB?

  1. #1
    Fred Giesen Guest

    Clearing Pivot table fields "memory" in VB?


    I have found when looping through a pivot table items field it has a memory
    of what is no longer in the data. How do I get rid of old items references?

    Scenario:

    I import external data into a pivot table, in VB I loop through the
    Machine field to build arrays of common machine names. I have found machines
    which are no longer in the database still appear in the list. (database is
    a comma delimited text file)

    for example machine name "FX1" should be named "FX01" I corrected the
    name in the raw data file and refreshed the pivot table. The next time I
    run the program both "FX1" and "FX01" appear in the list of machines. (only
    within VB)
    I'm positive FX1 does not appear in the raw data, I've expanded the pivot
    table to show everything (selected all in every page field selection) a
    search did not find any occurance of "FX1"

    'excerpt of relevant code:

    Public mypivot As PivotTable
    Public myfield As PivotField
    Public myitem As PivotItem
    Public curmach As PivotItem
    Public fxmachines(100)

    Set mypivot = ActiveSheet.PivotTables("site_avail_pivot")
    Set myfield = mypivot.PivotFields("MACHINE")

    For Each curmach In myfield.PivotItems
    If (curmach Like "FX*") Then
    fxmachines(fxcntr) = curmach
    fxcntr = fxcntr + 1
    End If
    Share on Google+

  2. #2
    Tim Patrick Guest

    Re: Clearing Pivot table fields "memory" in VB?


    As this question is outside of the scope of the discussion topic, I must pass
    on trying to determine the solution to the problem. However, I will make
    some general comments about this issue.

    Although you say that "FX1" no longer exists within the PivotTable, it must
    exist somewhere, or it would not show up. In this scenario, I would step
    through the code to find the location where one of the Pivot Items was "FX1."
    You could insert some code such as the following just after your If statement.

    If (curmatch = "FX1") Then Stop

    You may also wish to use the Immediate / Debug / Locals windows to examine
    the Pivot Table structure in more detail. Unfortunately, without actually
    being there to examine the Pivot Table and the data, it is difficult to determine
    the cause of the problem.

    -----
    Tim Patrick
    tim@vbstyleguide.com

    Share on Google+

  3. #3
    Fred Giesen Guest

    Re: Clearing Pivot table fields "memory" in VB?


    I had done that, stepped through the code and watched it build the array,
    using debug.print and watches, even with only 1 record in my database. Double
    clicking on the pivot table to view data, only shows 1 data record.

    thanks anyway,
    Fred.


    "Tim Patrick" <tim@vbstyleguide.com> wrote:
    >
    >As this question is outside of the scope of the discussion topic, I must

    pass
    >on trying to determine the solution to the problem. However, I will make
    >some general comments about this issue.
    >
    >Although you say that "FX1" no longer exists within the PivotTable, it must
    >exist somewhere, or it would not show up. In this scenario, I would step
    >through the code to find the location where one of the Pivot Items was "FX1."
    > You could insert some code such as the following just after your If statement.
    >
    > If (curmatch = "FX1") Then Stop
    >
    >You may also wish to use the Immediate / Debug / Locals windows to examine
    >the Pivot Table structure in more detail. Unfortunately, without actually
    >being there to examine the Pivot Table and the data, it is difficult to

    determine
    >the cause of the problem.
    >
    >-----
    >Tim Patrick
    >tim@vbstyleguide.com
    >


    Share on Google+

Similar Threads

  1. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 06:24 PM
  2. Re: (No subject)
    By Joe in forum Database
    Replies: 0
    Last Post: 04-04-2003, 06:13 PM
  3. How would u do it (design question) ?
    By Joe in forum Database
    Replies: 17
    Last Post: 04-04-2003, 05:55 PM
  4. Pivot Table HELP!!!
    By olaf in forum ASP.NET
    Replies: 0
    Last Post: 12-05-2000, 02:52 PM
  5. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center