VBA Pivot.items ausblenden
Philipp
ich würde gerne beim Aufbau einer Pivot Tabelle in VBA nur einzelne
ausgewählte PivotItems auswählen. Der Makro Editor hat mir eine
Negativabgrenzung vorgeschlagen, die folgendermaßen aussieht:
With ptTable.PivotFields("UP")
.PivotItems("105").Visible = False
.PivotItems("120").Visible = False
.PivotItems("125").Visible = False
etc....
Da ich allerdings sehr viele Items habe und diese je nach Datenbasis auch
noch variabel sind, stelle ich mir statt der Negativabgrenzung eine
Positvabgrenzung vor, die folgendermaßen aussehen könnte:
1. alle Pivot Items ausblenden
2. .PivotItems("505").Visible = True
3. .PivotItems("605").Visible = True
Ist so etwas grundsätzlich möglich und wie würde ein solcher Code aussehen?
Vielen Dank für Eure Hilfe,
Philipp
Werner Wagner
alle Items ausblenden:
For Each Element ptTable.PivotFields("UP").PivotItems
Element.Visible = False)
Next Element
Werner
"Philipp" <gnau...@gmx.de> schrieb im Newsbeitrag
news:BA037786-92B5-4C0F...@microsoft.com...
Thomas Ramel
Philipp schrieb am 18.03.2009
> ich würde gerne beim Aufbau einer Pivot Tabelle in VBA nur einzelne
> ausgewählte PivotItems auswählen. Der Makro Editor hat mir eine
> Negativabgrenzung vorgeschlagen, die folgendermaßen aussieht:
>
> With ptTable.PivotFields("UP")
> .PivotItems("105").Visible = False
> .PivotItems("120").Visible = False
> .PivotItems("125").Visible = False
> etc....
>
> Da ich allerdings sehr viele Items habe und diese je nach Datenbasis auch
> noch variabel sind, stelle ich mir statt der Negativabgrenzung eine
> Positvabgrenzung vor, die folgendermaßen aussehen könnte:
>
> 1. alle Pivot Items ausblenden
Das geht nicht - mindestens *ein* Pivot-Item muss immer sichtbar bleiben.
> 2. .PivotItems("505").Visible = True
> 3. .PivotItems("605").Visible = True
>
> Ist so etwas grundsätzlich möglich und wie würde ein solcher Code aussehen?
Ohne das was der Recorder aufzeichnet geht es nicht.
Du muss wie Werner schon schrieb mit einer Schleife alle Items prüfen und
die nicht gewünschten ausblenden, die gewünschten aber einblenden.
....oder arbeitest Du zufällig mit xl2007?
Dann gäbe es die Möglichkeit mit einem Array zu arbeiten, leider
unterschlägst Du aber gerade diese Info... ;-)
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Vista Ultimate SP-1 / xl2007 SP-1]
Philipp
angenommen ich würde die "120" behalten wollen, wie könnte das dann
aussehen?
For Each Element ptTable.PivotFields("UP").PivotItems
else Element.Visible = False
Next Element
"Werner Wagner" <wagn...@hotmail.com> schrieb im Newsbeitrag
news:OoQVmNA...@TK2MSFTNGP06.phx.gbl...
Philipp
Dim Element as Object?
"Philipp" <gnau...@gmx.de> schrieb im Newsbeitrag
news:Ohqct4Aq...@TK2MSFTNGP03.phx.gbl...
Thomas Ramel
Philipp schrieb am 18.03.2009
> Excel 2007 habe ich nicht => es wird wohl auf die Schleife hinauslaufen,
> angenommen ich würde die "120" behalten wollen, wie könnte das dann
> aussehen?
> For Each Element ptTable.PivotFields("UP").PivotItems
> if Element ="120" then Next Element
> else Element.Visible = False
> Next Element
Versuche es mal mit der folgenden Variante:
Dim oItem As PivotItem
Set pt = ActiveSheet.PivotTables(1)
For Each oItem In ptTable.PivotFields("UP").PivotItems
If oItem.Name = "120" Then
oItem.Visible = True
Next oItem
Alternativ, und das ist vielleicht wirklich zu überdenken - könntest Du in
der Datenbasis eine Zilfsspalte anlegen die sich z.B. an einer kleinen
Tabelle mit der Auswahl 'bedient' und für jedes gewollte Item eine Marke
setze.
Diese Hilfsspalte nimmst Du dann als zusätzliches Feld in die PT auf und
platzierst es im Seibenbereich.
Nun kannst Du durch Hinzufügen/Wegnemen von Items in der kleinen Tabelle
sehr einfach bestimmen welche Datensätze gezeigt werden sollen und welche
nicht - die PT muss nur anschliessend aktualisiert werden und Du hast erst
noch den Vorteil, dass Du auch mal alle Daten anzeigen lassen kannst oder
mehrere Sets erstellen.
Dies scheint mir der robustere Weg zu sein, als eine ganze PT über alle
Items abzuklappern, zumal das Ein-/Ausblenden jedesmal Zeit kostet.
Philipp
erst einmal vielen Dank, das mit der Schleife funktioniert schoneinmal, wenn
auch meine Pivot Tablle 20 Sekunden für den Aufbau braucht.
Ich bin mir nicht sicher, ob ich die Lösung mit der Spalte richtig
verstanden habe. Am besten ich beschreibe mal kurz, was ich eigentlich habe
und was ich machen möchte:
- meine Datenbasis besteht momentan aus ca. 25.000 Datensätzen
- hierbei handelt es sich um Positionen von ca. 100 Gesellschaften
- einlesen erfolgt bei mir über getOpenFilename
- anschließend werden die Datensätze noch bearbeitet und ergänzt über eine
Schleife über alle Zeilen
- u.a. werden dann Pärchen gebildet für Datensätze mit Partnerinformationen
z.B. 1-46 und 46-1
- nun möchte ich für JEDE Partnerkombinationen eine Übersicht erstellen mit
den gegenseitigen Positionen
- dies erfolgt über eine PivotTabelle
- da teilweise mehr Positionen habe wie für einzelne Aufstellung benötigt
werden habe ich die Datensätze in einer Spalte um ein Merkmal "Schuko"
ergänzt. dieses nehme ich dann als PageField und spare mir so das mühsame
ausblenden der Positionen.
- allerdings habe ich in meiner Datenbasis immer noch n zu m
Partnerkombinationen, die ich bei der Erstellung meines Pivots ausblenden
möchte, so dass z.B. nur 1-46 und 46-1 übrig bleibt.
- dies funktioniert mit der for each schleife über die PivotItems ganz gut,
aber nur recht langsam.
- Eine zusätzliche Spalte einzufügen wie bei der Sache mit den Positionen
funktioniert allerding m.E. in diesem Fall nicht ganz, da ich die Datenbasis
ja für alle Pivot Tabellen benutzen möchte.
- um Speicher zu Sparen benutze ich auch für alle den gleichen PivotCache
(oder bringt das far nichts?)
- Ansonsten käme natürlich noch in Frage die Datenbasis vor jeder Erstellung
einer Pivot Tabelle zu überarbeiten, dies wird dann aber auch nicht
schneller sein und ich würde für alle einen neuen Pivot Cache benötigen.
Hoffe es kam einigermaßen verständlich rüber. Am besten wäre soetwas gewesen
wie mit einem code "pivotItems.visible = false" alle auszublenden, aber das
scheint es leider nicht zu geben.
Falls noch jemand eine Idee würde ich mich freuen.
Grüße
Philipp
"Thomas Ramel" <t.r...@MVPs.org> schrieb im Newsbeitrag
news:142adj4wkc7vd.1...@40tude.net...
Thomas Ramel
Philipp schrieb am 19.03.2009
> erst einmal vielen Dank, das mit der Schleife funktioniert schoneinmal, wenn
> auch meine Pivot Tablle 20 Sekunden für den Aufbau braucht.
Ja, das ist leider so weil die PT nach jedem Ein-/Ausblenden eines Items
neu aufgebaut wird.
Du könntest noch versuchen die folgende Zeilen in deinen Code einzubauen,
das könnte den Ablauf noch etwas beschleunigen:
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpfdate = True
'Bearbeiten der PT-Items
pt.ManualUpfdate = False
> Ich bin mir nicht sicher, ob ich die Lösung mit der Spalte richtig
> verstanden habe. Am besten ich beschreibe mal kurz, was ich eigentlich habe
> und was ich machen möchte:
Ja, das ist immer eine seht gute Idee und Basis für die Lösungsfindung.
> - meine Datenbasis besteht momentan aus ca. 25.000 Datensätzen
> - hierbei handelt es sich um Positionen von ca. 100 Gesellschaften
> - einlesen erfolgt bei mir über getOpenFilename
> - anschließend werden die Datensätze noch bearbeitet und ergänzt über eine
> Schleife über alle Zeilen
> - u.a. werden dann Pärchen gebildet für Datensätze mit Partnerinformationen
> z.B. 1-46 und 46-1
> - nun möchte ich für JEDE Partnerkombinationen eine Übersicht erstellen mit
> den gegenseitigen Positionen
> - dies erfolgt über eine PivotTabelle
> - da teilweise mehr Positionen habe wie für einzelne Aufstellung benötigt
> werden habe ich die Datensätze in einer Spalte um ein Merkmal "Schuko"
> ergänzt. dieses nehme ich dann als PageField und spare mir so das mühsame
> ausblenden der Positionen.
OK, genau so hatte ich mir das auch mit der zusätzlichen Spalte vorgestellt
gehabt.
> - allerdings habe ich in meiner Datenbasis immer noch n zu m
> Partnerkombinationen, die ich bei der Erstellung meines Pivots ausblenden
> möchte, so dass z.B. nur 1-46 und 46-1 übrig bleibt.
> - dies funktioniert mit der for each schleife über die PivotItems ganz gut,
> aber nur recht langsam.
Ja, das ist verständlich
> - Eine zusätzliche Spalte einzufügen wie bei der Sache mit den Positionen
> funktioniert allerding m.E. in diesem Fall nicht ganz, da ich die Datenbasis
> ja für alle Pivot Tabellen benutzen möchte.
Kennst Du (oder kannst Du sie ermitteln) denn alle Partnerkombinationen?
Dann könntest Du für jede ein entsprechendes Kürzel in die weitere
Hilfsspalte antragen lassen - oder Du verwendest gleich die bisherige
übereidie Du ja einzelne Werte ausschliesst und schreibst das Kürzel für
die Kombination gleich dort mit rein.
Dann kannst Du nur noch das jeweilige Kürzel im Pagefield festlegen und gut
ist.
> - um Speicher zu Sparen benutze ich auch für alle den gleichen PivotCache
> (oder bringt das far nichts?)
Oh doch, das bringt einiges - so können z.b. Datumsgruppierungen auf einen
Schlag in allen 'angehängten' Pts' übernommen werden.
Und bei vielen PT's die auf dieselbe Quelle zugreifen wird deine Mappe so
schlanker bleiben.
> - Ansonsten käme natürlich noch in Frage die Datenbasis vor jeder Erstellung
> einer Pivot Tabelle zu überarbeiten, dies wird dann aber auch nicht
> schneller sein und ich würde für alle einen neuen Pivot Cache benötigen.
Ja, das ist korrekt - eigentlich müsstest Du dann die Basis mehrfach
anlegen da beim Aktualisieren einer PT diese die Daten neu aus dem Cache
einliest und dieser bei einer Manipulation der Quelldaten dann für alle
angehängten PT's verändert ist.
> Hoffe es kam einigermaßen verständlich rüber. Am besten wäre soetwas gewesen
> wie mit einem code "pivotItems.visible = false" alle auszublenden, aber das
> scheint es leider nicht zu geben.
Sowas geht erst ab xl2007 mit der dann die Eigenschaft
pvtTable.VisibleFields
zur Verfügung steht - dort kannst Du dieser Eigenschaft ein Array mit Items
übergeben, die dann sichtbar gemath werden.
> Falls noch jemand eine Idee würde ich mich freuen.
Ev. hilft es, wenn Du eine kleine Demo-Mappe mit einem 1:1 Beispiel und
verfälschten Daten zusammenstellen kannst, das Du dann auf einen webSpace
hochlädst damit wir es direkt betrachte können.
Andre
> Grüezi Philipp
>
> Philipp schrieb am 19.03.2009
>
> > erst einmal vielen Dank, das mit der Schleife funktioniert schoneinmal, wenn
> > auch meine Pivot Tablle 20 Sekunden für den Aufbau braucht.
>
> Ja, das ist leider so weil die PT nach jedem Ein-/Ausblenden eines Items
> neu aufgebaut wird.
Glaube ich nicht. Screenupdating=false ?
ein weitere Möglichkeit: "über" das zu filternde PivotFeld ein
beliebiges anderes setze (falls nicht schon eins da ist), dessen
Details dann ausblenden. Damit verschwinden deine wichtigen
pivotFelder, deren Eigenschaften solten jetzt aber immer noch
problemlos in der Schleife gesetzt werden können.
Danach Detail des Über-Elementes wieder setzen.
Gruß, Adnré
Thomas Ramel
Andre schrieb am 20.03.2009
> On 20 Mrz., 06:39, Thomas Ramel <t.ra...@MVPs.org> wrote:
>>
>> Philipp schrieb am 19.03.2009
>>
>>> erst einmal vielen Dank, das mit der Schleife funktioniert schoneinmal, wenn
>>> auch meine Pivot Tablle 20 Sekunden für den Aufbau braucht.
>>
>> Ja, das ist leider so weil die PT nach jedem Ein-/Ausblenden eines Items
>> neu aufgebaut wird.
>
> Glaube ich nicht. Screenupdating=false ?
...dann lässt Du es halt... ;-)
Ex geht hier nicht um das ScreenUpdating sondern um die Berechnung der
Pivot-Werte. Dies wird bei jedem Ein-/Ausblenden eines Pivot-Items gemacht.
> ein weitere Möglichkeit: "über" das zu filternde PivotFeld ein
> beliebiges anderes setze (falls nicht schon eins da ist), dessen
> Details dann ausblenden. Damit verschwinden deine wichtigen
> pivotFelder, deren Eigenschaften solten jetzt aber immer noch
> problemlos in der Schleife gesetzt werden können.
> Danach Detail des Über-Elementes wieder setzen.
Das könnte ein möglicher Ansatz sein.
Kannst Du uns bitte über deine Erfahrungen während des Testens berichten?
Philipp
habe am Freitag mein Programm mal laufen lassen, insgesamt habe ich nun ca
220 PivotTabellen/Reiter erstellt, Dauer ca. 30 Minuten.
Screenupdating hab ich drin.
Das mit dem pt.Manual.Update werde ich am Montag gleich mal einfügen, das
hört sich sehr gut an, ist ähnlich wie calculation.manual?
Wenn das nicht klappt probiere ich mal den Vorschlag von Andre.
Eine andere Frage habe ich noch.
Evtl. würde ich die Datenbasis noch während dem erstellen der pt teilweise
bearbeiten. Da ich für alle den gleichen Casch habe sollte eine
Aktualisierung am Ende doch gehen (wieder mit einer for each schleife). Ich
will keine Datensätze löschen, sondern nur einzelne hinzufügen. Oder meint
ihr das dauert dann wieder ewig, dann müsste ich die Bearbeitung gesondert
voran stellen, erst für alle Kombination überarbeitung, dann für alle
Pivots.
Ich halte Euch auf dem laufenden.
Grüße
Philipp
"Thomas Ramel" <t.r...@MVPs.org> schrieb im Newsbeitrag
news:1x497mb2rnpfe.1hf6ineiz3o6j$.dlg@40tude.net...
Andre
> Hallo Zusammen,
>
> habe am Freitag mein Programm mal laufen lassen, insgesamt habe ich nun ca
> 220 PivotTabellen/Reiter erstellt, Dauer ca. 30 Minuten.
>
> Screenupdating hab ich drin.
> Das mit dem pt.Manual.Update werde ich am Montag gleich mal einfügen, das
> hört sich sehr gut an, ist ähnlich wie calculation.manual?
>
> Wenn das nicht klappt probiere ich mal den Vorschlag von Andre.
> Eine andere Frage habe ich noch.
>
> Evtl. würde ich die Datenbasis noch während dem erstellen der pt teilweise
> bearbeiten. Da ich für alle den gleichen Casch habe sollte eine
> Aktualisierung am Ende doch gehen (wieder mit einer for each schleife). Ich
> will keine Datensätze löschen, sondern nur einzelne hinzufügen. Oder meint
> ihr das dauert dann wieder ewig, dann müsste ich die Bearbeitung gesondert
> voran stellen, erst für alle Kombination überarbeitung, dann für alle
> Pivots.
>
> Ich halte Euch auf dem laufenden.
>
> Grüße
> Philipp
>
Ich kann mir zwar unter deinem Problem noch nicht 100% was vorstellen,
aber du hast viele Tabellen erstellt. Wenn du diesen
Erstellungsvorgang häufiger machen musst kannst du noch folgendes
Probieren: Die zu summierende Spalte mit nichts belegen. Dann sollte
die Pivot-Tabelle wesentlich schneller beim Aufbau sein. Wenn alle
Pivottabelle angelegt sind die Spalte wieder mit den ursprünglichen
Werten belegen und den Cash aktualisieren.
Ein ActiveSheet.Parent.PivotCaches.Count vielleicht ab und zu mal zur
Kontrolle, ob es auch wirtklich nur noch ein Cache ist.
Philipp
nicht, dass es viel schneller geht.
Euch nochmals vielen Dank für die Hilfe!
Grüße
Philipp
"Andre" <infos...@gmx.net> schrieb im Newsbeitrag
news:c9c04c2a-23de-41c9...@g19g2000yql.googlegroups.com...
thomas...@gmail.com
On 26 Mrz., 21:07, "Philipp" <gnauc...@gmx.de> wrote:
> pt.Manual.Update war die Lösung, brauche nun nur noch 5 Minuten, glaube
> nicht, dass es viel schneller geht.
Danke für deine Rückmeldung - ja ich denke auch, dass damit die
Grenzen über die Manipultion der fertigen PT ausgereizt sind.
Allenfalls die zusätzliche Hilfsspalte könnte noch etwas bringen - die
Formeln aktualiseren sich im Nu und beim Aktualisieren der PT sind die
neuen Informationen bereits im Cache vorhanden. Das sollte IMO weniger
Zeit in Anspruch nehmen.
Für die Werte in der Hilfsspalte müssten die genauen Kriterien bekannt
sein, wenn Du daher eine (abgespeckte) Demo-Mappe zur Verfügung sellen
köntest wäre das äusserst hilfreich.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für MS-Excel -