Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
VBA: Pivottable-Array aus Excel auslesen
19.10.2021 10:34:28
Andrea
Hallo zusammen,
aus dem Forum hier habe ich von Michael den folgenden Code übernommen:

Sub a()
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet: Set Ws = Wb.Worksheets("AfA")
Dim p As PivotTable: Set p = Ws.PivotTables("PivotTable2")
Dim f As PivotField: Set f = p.PivotFields("Monat")
Dim i As PivotItem
With f
For Each i In .PivotItems
Select Case i.Name
Case Is = "Januar", "Februar"
i.Visible = True
Case Else
i.Visible = False
End Select
Next
End With
End Sub
Leider funktioniert er bei mir nicht. Daraufhin habe ich mit dem Makrorekorder die Auswahl von Januar und Februar aufgezeichnet:

Sub b()
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = Array("[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]")
End Sub
und den Code wie folgt angepasst:

Sub a()
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet: Set Ws = Wb.Worksheets("AfA")
Dim p As PivotTable: Set p = Ws.PivotTables("PivotTable2")
Dim f As PivotField: Set f = p.PivotFields("[Datum].[Monat].[Monat]")
Dim i As PivotItem
With f
For Each i In .PivotItems
Select Case i.Name
Case Is = "[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]"
i.Visible = True
Case Else
i.Visible = False
End Select
Next
End With
End Sub
Leider funktioniert das auch nicht. Es kommt aber auch keine Fehlermeldung! Nur, dass das Pivotfeld "Monat" einfach auf dem Wert "All" stehen bleibt!? Was mache ich falsch?
Darüber hinaus hätte ich es gerne, dass die einzustellenden Monate nicht direkt im Code stehen, sondern aus Excel übernommen werden. Dazu habe ich in A1:B12 in Spalte A untereinander die Monatsnamen Januar bis Dezember und in Spalte B zu jedem Monat die Angabe "WAHR" oder "FALSCH". Mit dem Makro sollen die Monate in der Pivottabelle eingestellt werden, die in dieser Liste den Wert "WAHR" haben. Könnt ihr mir helfen?
Vielen Dank für eure Unterstützung.
Gruß Andrea

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 10:50:17
peterk
Hallo Andrea
Kannst Du eine Beispielmappe hochladen.
Peter
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 12:11:08
Andrea
Hallo Peter,
kann ich gerne machen. Ich weiß allerdings nicht, ob das viel nützt, da ich die Pivot nicht selbst erstellt habe, sondern sie aus unserem firmeneigenen BI bekomme. Und wenn ich hier die Mappe hochlade, wird ja die Verbindung zu dem BI verlorengehen und die Pivot arbeitet nicht mehr!? Ich hab die Datei dennoch beigefügt. Vielleicht hilft es ja trotzdem schon was, evtl. zum besseren Verständnis:
https://www.herber.de/bbs/user/148693.xlsm
Gruß Andrea
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 12:35:43
Yal
Hallo Andrea,
was spricht dagegen, den aufgenommenen Code zu verwenden?
Ersetze nur ActiveSheet durch ThisWorkbook.Worksheets("AfA")

Sub a()
ThisWorkbook.Worksheets("AfA").PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = Array("[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]")
End Sub
VG
Yal
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 12:44:32
Andrea
Hallo Yal,
dagegen spricht, dass der Anwender die Monate mittels Checkboxen frei wählen können soll (s. hochgeladene Beispieldatei von 12:11). Dazu habe ich in A1:B12 in Spalte A untereinander die Monatsnamen Januar bis Dezember und in Spalte B zu jedem Monat die Angabe "WAHR" oder "FALSCH" stehen. Mit dem Makro sollen die Monate in die Pivottabelle übertragen werden, die in dieser Liste den Wert "WAHR" haben.
VG Andrea
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 13:28:40
Yal
Hallo Andrea,
Ich erkenne nicht die Unterschied zwischen den von Dir ursprunglich geposteten Code und meinen Vorschlag. Die neueste Anforderungen waren nicht in der erste Frage vorhanden (und entsprechen auch nicht dein Code).
Aber weil ich mich gut mit VBA auskenne, kenne ich alle Probleme von VBA und weisse, dass VBA vermieden werden soll, wenn:
_ eine Lösung direkt aus Excel existiert: Hast Du den Slicer angeschaut?
_ VBA für die Faulheit oder der Mangel an Wissen der Users einspringen muss: schule deine User, mit Pivottabelle umzugehen anstatt Sie zu schulen, was ein Mail ans Support beinhalten soll, wenn der VBA nicht funktioniert wie erwartet.
(die Anzeichen von Situation 2 sind deutlich vorhanden)
Es hört sich frech meinerseits und es wird sicher viele und gute Grunde geben, warum Du doch an deinem Weg festhalten muss (bzw. will). Aber es gibt immer mehrere Wege zur Lösung. Keine perfekte, aber manche sind auf lange Sicht wesentlich teuerer.
VG
Yal
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 13:38:34
Andrea
Hallo Yal,
das stimmt so nicht ganz. Ich hatte schon in meinem ersten Post erwähnt, dass ich das mit dem Übernehmen aus Excel gerne hätte, aber nicht weiß wie ich es per VBA umsetzen kann. (So heißt ja auch der Betreff des Beitrags :-)
Slicer ist auch keine Lösung, da es in Wahrheit nicht nur um eine Pivottabelle geht, die entsprechend eingestellt werden soll, sondern um 16, was manuell per Slicer erheblichen Aufwand bedeuten würde, den ich mir als Hauptanwender gerne ersparen würde.
Wenn also jemand eine Idee hat ...
Danke und Gruß Andrea
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 15:08:15
Yal
... Dann ist eben das Wissenmangel nicht bei deinen User, sondern bei Dir :-)
ein Slicer kann tatsächlich mehreren Pivottabelle steuern.
VG
Yal
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 15:19:25
Andrea
Das mag stimmen, deshalb habe ich mich ja hilfesuchend an die Schlauen hier im Forum gewandt.
Durch diese Art von Antworten werde ich allerdings auch nicht klüger :-(
Es wäre schön, wenn mir jemand erklären könnte, warum der Code offensichtlich bei anderen läuft, bei mir aber nicht.
Vielen Dank
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 15:54:45
Andrea
Ich habe jetzt mal selbst eine Pivottabelle erstellt und dabei ein Pivotfeld namens "Monat" benutzt. Hier funktioniert der Code von Michael wie vermutet einwandfrei.
Es muss also an den Bezeichnungen von Pivotfield und Pivotitem liegen, wie sie mir aus dem BI zur Verfügung gestellt werden. Kann ich die verwandten Bezeichnungen irgendwie auslesen? Wenn ich auf dem Pivotfield der Tabelle stehe, sagt mit Excel oben im Ribbon unter PivotTable-Analyse, das aktive Feld hieße "Monat". Verwende ich allerdings diese Bezeichnung, meckert der Debugger schon bei der Variablen-Zuordnung

Set f = p.PivotFields("Monat")
Setze ich statt dessen wie mit dem Makrorekorder aufgezeichnet

Set f = p.PivotFields("[Datum].[Monat].[Monat]")
ein läuft das Makro durch, tut allerdings nichts.
Demnach kann es doch nur noch an i.Name liegen. Wenn aber der Makrorecorder doch aufzeichnet:

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Datum].[Monat].[Monat]"). _
VisibleItemsList = Array("[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]")
wieso passt dann

With f
For Each i In .PivotItems
Select Case i.Name
Case Is = "[Datum].[Monat].&[1]", "[Datum].[Monat].&[2]"
i.Visible = True
Case Else
i.Visible = False
End Select
Next
End With
nicht?
Hat keiner eine Idee?
Andrea
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 16:49:28
peterk
Hallo Andrea
PivotFields("[Datum].[Monat].[Monat]") hat leider keine PivotItems.
Mit folgendem Code

Sub a()
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet: Set Ws = Wb.Worksheets("AfA")
Dim p As PivotTable: Set p = Ws.PivotTables("PivotTable2")
Dim f As PivotField
Dim i As PivotItem
For Each f In p.PivotFields
Select Case f.Orientation
Case 0: Debug.Print ("xlHidden"),
Case 1: Debug.Print ("xlColumnField"),
Case 2: Debug.Print ("xlHidden"),
Case 3: Debug.Print ("xlPageField"),
Case 4: Debug.Print ("xlDataField"),
End Select
Debug.Print f.Name
For Each i In f.PivotItems
Debug.Print "                           ", i.Name
Next
Next
End Sub
bekommst Du diesen Output

xlHidden      [Rechnungsbestandteil].[Entgelte].[Entgeltart]
xlHidden      [Rechnungsbestandteil].[Entgelte].[Geschäftsvorfall]
xlHidden      [Rechnungsbestandteil].[Entgelte].[Geschäftsvorfall].[Entgeltart]
xlHidden      [Rechnungsbestandteil].[Geschäftsvorfall].[Geschäftsvorfall]
xlHidden      [Rechnungsbestandteil].[Geschäftsvorfall].[Geschäftsvorfall].[Entgeltart]
xlColumnField [Konto].[Kontonummer].[Kontonummer]
[Konto].[Kontonummer].&[125]
[Konto].[Kontonummer].&[184]
[Konto].[Kontonummer].&[100]
[Konto].[Kontonummer].&[402]
[Konto].[Kontonummer].&[403]
[Konto].[Kontonummer].&[101]
[Konto].[Kontonummer].&[66]
[Konto].[Kontonummer].&[68]
[Konto].[Kontonummer].&[63]
[Konto].[Kontonummer].&[67]
[Konto].[Kontonummer].&[53]
[Konto].[Kontonummer].&[60]
[Konto].[Kontonummer].&[374]
[Konto].[Kontonummer].&[376]
[Konto].[Kontonummer].&[61]
[Konto].[Kontonummer].&[375]
[Konto].[Kontonummer].&[373]
[Konto].[Kontonummer].&[40]
[Konto].[Kontonummer].&[69]
[Konto].[Kontonummer].&[64]
[Konto].[Kontonummer].&[126]
[Konto].[Kontonummer].&[185]
[Konto].[Kontonummer].&[242]
[Konto].[Kontonummer].&[12]
[Konto].[Kontonummer].&[417]
[Konto].[Kontonummer].&[238]
xlHidden      [Konto].[Kontonummer].[Kontonummer].[Kontentyp]
Sachkonto
xlHidden      [Konto].[Kontonummer].[Kontonummer].[Konto Bezeichnung]
Abschreibungen auf Kunst
Abschreibungen auf immaterielle Vermögensgegenstände
Abschreibungen auf Geschäftsausstattung
Abschreibungen auf Büroeinrichtung
Abgänge Betriebsvermögen Kanzlei
Abschreibungen auf Hardware
Abschreibungen auf Einbauten
xlHidden      [Standort Buchung].[Standort].[Standort]
[Standort Buchung].[Standort].&[6]
[Standort Buchung].[Standort].&[3]
[Standort Buchung].[Standort].&[7]
[Standort Buchung].[Standort].&[4]
[Standort Buchung].[Standort].&[1]
[Standort Buchung].[Standort].&[2]
[Standort Buchung].[Standort].&[5]
xlDataField   [Measures].[Buchungsbetrag]
xlHidden      [Buchungsperiode].[Periodennummer].[Periodennummer]
xlHidden      [Buchungsperiode].[Geschäftsjahrkürzel].[Geschäftsjahrkürzel]
xlColumnField [Konto].[Kontobezeichnung].[Kontobezeichnung]
[Konto].[Kontobezeichnung].&[Abgänge Betriebsvermögen Kanzlei]
[Konto].[Kontobezeichnung].&[Abschreibungen auf Büroeinrichtung]
[Konto].[Kontobezeichnung].&[Abschreibungen auf Einbauten]
[Konto].[Kontobezeichnung].&[Abschreibungen auf Geschäftsausstattung]
[Konto].[Kontobezeichnung].&[Abschreibungen auf Hardware]
[Konto].[Kontobezeichnung].&[Abschreibungen auf immaterielle Vermögensgegenstände]
[Konto].[Kontobezeichnung].&[Abschreibungen auf Kunst]
[Konto].[Kontobezeichnung].&[Hardware]
[Konto].[Kontobezeichnung].&[Kunst]
[Konto].[Kontobezeichnung].&[Werbekosten]
xlPageField   [Versteuerungsart].[Versteuerungsart].[Versteuerungsart]
xlHidden      [Buchung].[Buchungsnummer].[Buchungsnummer]
[Buchung].[Buchungsnummer].&[29048]
xlHidden      [Buchung].[Buchungsnummer].[Buchungsnummer].[Buchungsart]
xlHidden      [Buchung].[Buchungsnummer].[Buchungsnummer].[Buchungstext]
xlColumnField [Buchungsmenge].[Art].[Kontenart]
[Buchungsmenge].[Art].[Kontenart].&[Konto]
[Buchungsmenge].[Art].[Kontenart].&[EA-Konto]
xlHidden      [Buchungsmenge].[Art].[Kontenart Detail]
xlHidden      [Buchungsmenge].[Art].[Kontenart Detail].[Kontenart]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Bezeichnung]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Datum Bis]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Datum Von]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Geschaeftsjahrkuerzel]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Offen]
xlHidden      [Buchungsperiode].[Buchungsperiode].[Buchungsperiode].[Periodennummer]
xlPageField   [Datum].[Jahr].[Jahr]
xlHidden      [Buchung].[Buchungsnummer].[Buchungsnummer].[Beleg1]
xlHidden      [Buchung].[Buchungsnummer].[Buchungsnummer].[Beleg2]
xlPageField   [Datum].[Monat].[Monat]
Zeilen mir führende Blanks sind PivotItems. PivotFields vom Type xlPageField haben leider keine.
Peter
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 17:03:39
Andrea
Lieber Peter,
ich danke dir für deine Mühe! Jetzt weiß ich zumindest warum es mit dem Code mittels Item nicht funktioniert.
Siehst du bei dieser Konstellation der Pivottabelle eine andere Möglichkeit, den Monat per VBA aus der Excel-Liste zu erstellen?
VG Andrea
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 15:57:47
Yal
Hallo Andrea,
ja, da hast Du recht.
Mit einem Rechtsklick auf dem Feld Datum.Monat wählst Du "Als Datenschnitt hinzufügen". Wenn diese ausgewählt ist, erscheint das Menü "Datenschnitttools". Dort wählst Du "Berichtsverbindungen" und aktiviere die Pivottabellen, die Du mit diesem Slicer steuern möchtest.
VG
Yal
Anzeige
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 17:07:18
Andrea
Hallo Yal,
danke, das war neu für mich! Hab ich heute doch noch was dazu gelernt :-)
Dann muss ich es eben so machen, obwohl die Code-Variante - sofern sie funktionieren würde - mir natürlich noch viele weitere Möglichkeiten eröffnet hätte.
Aber auch die Datenschnitt-Variante ist für mich hilfreich.
VG Andrea
AW: VBA: Pivottable-Array aus Excel auslesen
19.10.2021 18:19:04
Yal
Hallo Andrea,
Vielen Dank für die Rückmeldung und schon, dass ich Dir doch hilfreich sein könnte.
Du kannst weiterhin den Slicer per VBA steuern. Aber wie gesagt: mit VBA holst Du dir oft mehr potentielle Fehlerquelle als es eine Problem löst. Solang Du die Datei nur allein verwendest, alles im Griff. Bei Lösungen, die bei Kollegen verteilt werden, dann wird dein telefonnummer sehr schnell sehr bekannt.
Ein Analysis Services Cube kannst Du ebenfalls per Power Query abfragen. Auch sehr stabil und ohne VBA.
VG
Yal
Anzeige

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige