Clearing Pivot table fields "memory" in VB?

[Fred Giesen]

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

[Tim Patrick]

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

[Fred Giesen]

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
>