Microsoft Office Forum [ www.Office-Fragen.de ] >> READONLY <<

Microsoft Office 2003-2019 => Excel => Thema gestartet von: ae am Dezember 06, 2011, 18:05:33 Nachmittag

Titel: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 06, 2011, 18:05:33 Nachmittag
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?

Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Officer am Dezember 06, 2011, 20:36:08 Nachmittag
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

Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 08, 2011, 07:06:37 Vormittag
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.
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Officer am Dezember 08, 2011, 09:23:15 Vormittag
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 (http://support.microsoft.com/kb/202232)

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

LG

Alois
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 08, 2011, 10:20:01 Vormittag
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
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Officer am Dezember 08, 2011, 10:51:59 Vormittag
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
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 08, 2011, 10:55:22 Vormittag
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 !!
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Officer am Dezember 08, 2011, 10:57:15 Vormittag
Hi,

Zitat
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
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Officer am Dezember 08, 2011, 10:58:37 Vormittag
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
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Thomas Ramel am Dezember 08, 2011, 15:29:35 Nachmittag
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.
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 08, 2011, 15:51:04 Nachmittag
Hallo Thomas,

Zitat
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 -
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Thomas Ramel am Dezember 08, 2011, 15:55:55 Nachmittag
Grüezi Andreas

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

Zitat
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...? ;-)
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: ae am Dezember 08, 2011, 15:59:23 Nachmittag
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
Titel: Antw: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: IljaMett am Mai 08, 2013, 13:56:39 Nachmittag
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
Titel: Antw: Pivottabelle merkt sich "alte" Datensätze
Beitrag von: Thomas Ramel am Mai 08, 2013, 17:17:58 Nachmittag
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