Pivottabelle merkt sich "alte" Datensätze

[ae]

Hallo zusammen,

eine Pivottabelle bezieht Ihre Werte aus einer Tabelle die sich permanent ändert.
Das funktioniert soweit auch alles bestens und beim Aktivieren des Sheet mit der Pivot aktualisiere ich diese auch

Code: Visual Basic

1. Private Sub Worksheet_Activate()
2. Dim pt As PivotTable
3.  
4. For Each pt In Me.PivotTables
5.    
6.     pt.RefreshTable
7.  
8. Next
9. End Sub
10.  

Funktioniert soweit auch. Allerdings ist es seltsam, dass wenn ich zB in den Zeilenfelder das Dropdown "Lieferant" öffne, dort noch Datensätze bzw. Lieferantennamen enthalten sind, welche längst gelöscht sind.

Wähle ich hier zB den Lieferant "du" werden mir (logischerweise) keine Sätze gelistet, da dies Testdaten waren, die längst gelöscht wurden.

Wie bekomme ich Excel dazu diese nicht mehr vorhandenen Lieferanten auch nicht mehr anzuzeigen?

[Officer]

Hallo Andreas,

es wäre gut, wenn du mir die Excel-Tabelle am besten per Mail zusenden bzw. hier uploaden könntest. Gerne auch anonymisiert, dann schau ich mir´s mal an.

Viele Grüße

Alois

[ae]

Hallo Alois,

sorry, eben erst gesehen im Stress.
Ich hab mal ein Muster gemacht mit einigen wenigen Datensätzen.
Diese liegen in der Tabelle Liste.
Hier gab es die Kunden 1-4 sowie die Löschkunden 1-2
Pivot gemacht - alles gut.
Dann die Datensätze des "Löschkunden 1 gelöscht
Pivot aktualisiert
Das ist der Stand der Arbeitsmappe wie ich es hochgeladen habe.
Wählst Du das Dropdown in der Pivot beim Kunde erschjeint immer noch der Löschkunde 1.
Irgendwie seltsam.

[Officer]

Hallo Andreas,

das ist ein seit längerem bekanntes Problem, bereits ab Excel 97, welches meines Wissens ab Excel 2007 behoben wurde. Habs aber noch nicht getestet.

Lass einfach mal folgendes Makro laufen. Damit sollte das Problem behoben sein.

Code: Visual Basic

1. Sub DeleteOldPivotItemsWB()
2. '** Löschen von nicht mehr vorhandenen Listeneinträgen in Pivot-Tabellen MSKB (Q202232)
3. Dim wS             As Worksheet
4. Dim pt              As PivotTable
5. Dim pf              As PivotField
6. Dim pi              As PivotItem
7.  
8. '** Errorhandling
9. On Error Resume Next
10.  
11. For Each wS In ActiveWorkbook.Worksheets
12.   For Each pt In wS.PivotTables
13.     pt.RefreshTable
14.     For Each pf In pt.PivotFields
15.       For Each pi In pf.PivotItems
16.        If pi.RecordCount = 0 And _
17.           Not pi.IsCalculated Then
18.           pi.Delete
19.        End If
20.       Next
21.     Next
22.   Next
23. Next
24.  
25. 'Hier für Excel ab 2002
26. For Each wS In ActiveWorkbook.Worksheets
27.   For Each pt In wS.PivotTables
28.      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
29.   Next pt
30. Next wS
31.  
32. End Sub
33.  

Siehe auch hier: http://support.microsoft.com/kb/202232

Konntest Du unter 2007 bzw. 2010 dieses Phänomen auch schon mal beobachten?

LG

Alois

[ae]

Hallo Alois,
vielen Dank!
Ist mir seither nie aufgefallen.
Zu Office2010 kann ich momentan nur sagen, dass wenn ich es in Excel2010 öffne sich genauso verhält. Das hatte ich heute morgen kurz schon gemacht.
Allerdings keine neue Datei sondern die unter 2003 erstellte.

Eben habe ich dann eine neue Datei unter 2010 erstellt. Zunächst gespeichert als altes Dateiformat - Das Problem bleibt erhalten.

Dann habe ich die Datei erneut gemacht - gespeichert als 2010 Format - und auch hier - selbes Spiel - siehe Datei in der Anlage

[Officer]

Hi Andreas,

ich hab mal nachgesehen. Unter Excel 2010 gibt es eine Einstellungsmöglichkeit, siehe Screenshot. Damit werden gelöschte Elemente nicht mehr im Filter angezeigt.

Gruße

Alois

[ae]

Danke !!! Das löst dann wenigstens das Problem in 2010

Und bei der 2003 habe ich Dein Makro eingebaut und wie so oft - kaum macht man es richtig ... schon funktioniert es !!

[Officer]

Hi,

und wie so oft - kaum macht man es richtig ... schon funktioniert es !!

Das hab ich auch schon mal gehört und vor allem schon oft erfahren!

LG

Alois

[Officer]

Für die Allgemeinheit habe ich das Problem hier mal schnell zusammengefasst:

http://www.excel-inside.de/vba-loesungen/sonstiges/464-geloeschte-elemente-aus-pivottable-auswahl-entfernen.html

Gruß

Officer

P.S. Autor des Codes ist Thomas Rammel

[Thomas Ramel]

Grüezi zusammen

...darf ich auch als Quelle des Codes noch genannt werden... ?  ;-) ;-)
So steht er bei mir in der Makro-Arbeitsmappe.

Code: Visual Basic

1. Sub DeleteOldPivotItemsWB()
2. 'löschen von nicht mehr verwendeten Einträgen in Pivot-Tabellen
3. 'basierend auf MSKB (Q202232)
4. Dim ws                        As Worksheet
5. Dim pt                        As PivotTable
6. Dim pF                        As PivotField
7. Dim pI                        As PivotItem
8.  
9.     If Application.Version < 10 Then
10.         'Für alle älteren Excel-Versionen
11.        For Each ws In ActiveWorkbook.Worksheets
12.             For Each pt In ws.PivotTables
13.                 pt.RefreshTable
14.                 pt.ManualUpdate = True
15.                 For Each pF In pt.PivotFields
16.                     For Each pI In pF.PivotItems
17.                         If pI.RecordCount = 0 And _
18.                            Not pI.IsCalculated Then
19.                             pI.Delete
20.                         End If
21.                     Next
22.                 Next
23.                 pt.ManualUpdate = False
24.             Next
25.         Next
26.     Else
27.         'Als Alternative ab xl2002
28.        For Each ws In ActiveWorkbook.Worksheets
29.             For Each pt In ws.PivotTables
30.                 pt.ManualUpdate = True
31.                 pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
32.                 pt.RefreshTable
33.                 pt.ManualUpdate = False
34.             Next pt
35.         Next ws
36.     End If
37. End Sub

Und die Option zum einstellen steht auch in xl2007 schon über die Optionen der PT zur Verfügung, nicht erst seit xl2010.
Funktioniert hat das Setzen der Eigenschaft aber auch bereits in xl2002, das wird mit dem Code oben auch so gemacht.

[ae]

Hallo Thomas,

Funktioniert hat das Setzen der Eigenschaft aber auch bereits in xl2002, das wird mit dem Code oben auch so gemacht.

Habe eben mal den XP-Dialog geöffnet. Wo setze ich denn die Eigenschaften dort? Bin wohl wieder mal blind -

[Thomas Ramel]

Grüezi Andreas

...bitte nicht selektiv sondern im Kontext lesen... ;-)

Und die Option zum einstellen steht auch in xl2007 schon über die Optionen der PT zur Verfügung, nicht erst seit xl2010.
Funktioniert hat das Setzen der Eigenschaft aber auch bereits in xl2002, das wird mit dem Code oben auch so gemacht.

Seit xl2007 geht das über die Oberfläche.

Seit xl2002 gibt es diese Einstellung aber eben nur per VBA zu erreichen.

Vor xl2002 geht es nur über das Abklappern der einzelnen Items per VBA.


...ist das nun ein wenig verständlicher...? ;-)

[ae]

Hallo Thomas,

ok - dann hatte ich das falsch interpretiert.

Aber da ich eh über VBA automatisch aktualisiere wenn das Tabellenblatt  Pivot angewählt wird  nutze ich einfach den Code - dann passt das auch.

Vielen Dank

[IljaMett]

Hallo zusammen,

Ich bin gerade bei der Suche nach einer Lösung für das gleiche Problem auf diesen Beitrag gestoßen.
Eine Verständnisfrage: Sollte bei Office2003 nur der entsprechende Teil ausgeführt werden, also so?

Code: Visual Basic

1. Sub DeleteOldPivotItemsWB()
2. '** Löschen von nicht mehr vorhandenen Listeneinträgen in Pivot-Tabellen MSKB (Q202232)
3. Dim wS             As Worksheet
4. Dim pt              As PivotTable
5. Dim pf              As PivotField
6. Dim pi              As PivotItem
7.  
8. '** Errorhandling
9. On Error Resume Next
10.  
11. 'Hier für Excel ab 2002
12. For Each wS In ActiveWorkbook.Worksheets
13.   For Each pt In wS.PivotTables
14.      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
15.   Next pt
16. Next wS
17.  
18. End Sub
19.  

Das hat bei meiner Tabelle keine Wirkung.

Wenn ich das komplette Macro laufen lasse, stürzt Excel dabei jedes Mal ab - möglicherweise ist die Tabelle dafür zu groß oder es liegt daran, dass die Daten über eine Access-DB eingelesen werden.

Ich würde mich über eine Rückmeldung dazu freuen.

Ilja

[Thomas Ramel]

Grüezi Ilja

das Makro ist eigentlich so gestaltet, dass es selbst erkennt welchen Teil es ausführen soll.

Hier die aktuelle Version von meinem PC:

Code: [Auswählen]

Sub DeleteOldPivotItemsWB()
'löschen von nicht mehr verwendeten Einträgen in Pivot-Tabellen
'basierend auf MSKB (Q202232)
'Quelle: Thomas Ramel
Dim ws                   As Worksheet
Dim pt                   As PivotTable
Dim pf                   As PivotField
Dim pi                   As PivotItem

On Error Resume Next

'als Alternative ab xl2002
If Val(Application.Version) > 9 Then
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.ManualUpdate = True
            pt.RefreshTable
            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            pt.ManualUpdate = False
        Next pt
    Next ws
Else
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.ManualUpdate = True
            pt.RefreshTable
            For Each pf In pt.PivotFields
                For Each pi In pf.PivotItems
                    If pi.RecordCount = 0 And _
                       Not pi.IsCalculated Then
                        pi.Delete
                    End If
                Next pi
            Next pf
            pt.ManualUpdate = False
        Next pt
    Next ws
End If
End Sub