Office: VBA - Kann ein PivotItem nicht einem Object zuweisen

Hallo zusammen,

irgendwie verstehe ich das Problem mit dem PivotItem nicht.

bei diesem Teil des Codes (zweites Sub) funktioniert die Set Anweisung

Code:

 Set PivotElementMin = PivotFeld.PivotItems(PElementMin)

aber irgendwie funktioniert die gleiche Set Anweisung bei diesem Part nicht und eine Fehlermeldung erscheint ("Die PivotItem Eigenschaft ... kann nicht zugeordnet werden."

Code:

Set PivotElementMax = PivotFeld.PivotItems(PElementMax) 'Fehlermeldung 

Hat einer von eine Idee wie ich das umgehen kann?

Schöne Grüße
Tauchmann

Code:

Option Explicit
Sub bla()
Dim ReportingWeekStart As String, ReportingWeekEnd As String

ReportingWeekStart = Worksheets("Data For Analysis").Range("A3")
ReportingWeekEnd = Worksheets("Data For Analysis").Range("A4")

PivotSetzen "Tabelle6", "PivotTable2", "WW", "xlRowField", "2012-32", "2012-34"

End Sub


Public Sub PivotSetzen(PBlatt As String, PTabelle As String, PFeld As String, _
    PFeldOrientation As String, Optional PElementMin As String, Optional PElementMax As String)

Dim ArbeitsBlatt As Worksheet, PivotTabelle As PivotTable
Dim PivotFeld As PivotField, PivotElement As PivotItem
Dim PivotElementMin As PivotItem, PivotElementMax As PivotItem

Set ArbeitsBlatt = ActiveWorkbook.Worksheets(PBlatt)

Set PivotTabelle = ArbeitsBlatt.PivotTables(PTabelle)
    With PivotTabelle.PivotCache
        .MissingItemsLimit = xlMissingItemsNone
        .Refresh
    End With
    
Set PivotFeld = PivotTabelle.PivotFields(PFeld)
    With PivotFeld
        .Orientation = xlRowField
        .Position = 1
        .EnableMultiplePageItems = True
        .AutoSort xlAscending, PivotFeld
    End With
     
If PElementMin <> "" Or PElementMax <> "" Then
    Set PivotElementMin = PivotFeld.PivotItems(PElementMin)
    Set PivotElementMax = PivotFeld.PivotItems(PElementMax) 'Fehlermeldung
        With PivotElementMin
            .Visible = True
        End With
    Else
        MsgBox "Einen Zeitraumwert wurde nicht angegeben!"
End If
    
End Sub

 

Hallo Tauchmann,

des Rätsels Lösung dürfte einfach sein. Du übergibst an die Sub "Pivotsetzen" an den Aufrufparameter "PElementMin" den Wert "2012-32" und an den Aufrufparameter "PElementMax" den Wert "2012-34". Sieht man in der PivotTable "PivotTable2" des Tabellenblattes "Tabelle6" nach, so ist dort zwar der Wert für PElementMin zu finden, jedoch nicht der Wert für PElementMax.
Wenn Du testenshalber an vorgenannte Sub statt "2012-34" den Wert "2012-33" übergibst, dann läuft die Sub störungsfrei durch, denn dieser Parameterwert existiert tatsächlich in der PT des Blattes "Tabelle6", wie in der folgenden PT-Darstellung zu sehen ist:

Arbeitsblatt mit dem Namen 'Tabelle6'
	A
2
3	Zeilenbeschriftungen
4	2012-30
5	99
6	2012-31
7	21
8	33
9	2012-32
10	33
11	44
12	66
13	2012-33
14	22
15	35
16	Gesamtergebnis

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

Hallo Exl121150,

vielen Dank für die Hilfe. Manchmal ist man einfach blind.
Nun habe ich ein weiteres Problem.

Im ersten Sub möchte ich meine Pivot Sachen definieren und im zweiten Sub alle Pivotfelder dann einstellen lassen.
Da ich nun die Orientation der Pivotfields brauche, habe ich im ersten Sub die Orientation als String definiert.
Das Problem ist nun das der String "xlRowField" im zweiten Sub ohne Anführungsstriche benötigt wird also so:

With PivotFeld
.Orientation = xlRowField
End With
.

Kann man das irgendwie hinbekommen???

Code:

Option Explicit
Sub bla()
Dim ReportingWeek1 As String, ReportingWeek2 As String
Dim ReportingWeek3 As String, ReportingWeek4 As String, ReportingWeek5 As String
ReportingWeek1 = Worksheets("Data For Analysis").Range("A1") & "-" & Worksheets("Data For Analysis").Range("A3")
ReportingWeek2 = Worksheets("Data For Analysis").Range("A1") & "-" & Worksheets("Data For Analysis").Range("A4")
ReportingWeek3 = Worksheets("Data For Analysis").Range("A1") & "-" & Worksheets("Data For Analysis").Range("A5")
ReportingWeek4 = Worksheets("Data For Analysis").Range("A1") & "-" & Worksheets("Data For Analysis").Range("A6")
ReportingWeek5 = Worksheets("Data For Analysis").Range("A1") & "-" & Worksheets("Data For Analysis").Range("A7")
PivotSetzen "Tabelle6", "PivotTable2", "Wert A", "xlDataField" '<-- PivotFeldOrientation definiert
PivotSetzen "Tabelle6", "PivotTable2", "WW", "xlRowField", _
    ReportingWeek1, ReportingWeek2, ReportingWeek3, ReportingWeek4, ReportingWeek5
End Sub
Public Sub PivotSetzen(PBlatt As String, PTabelle As String, PFeld As String, PFeldOrientation As String, _
    Optional RepWee1 As String, Optional RepWee2 As String, Optional RepWee3 As String, Optional RepWee4 As String, Optional RepWee5 As String)
Dim ArbeitsBlatt As Worksheet, PivotTabelle As PivotTable
Dim PivotFeld As PivotField, PivotElement As PivotItem
Set ArbeitsBlatt = ActiveWorkbook.Worksheets(PBlatt)
Set PivotTabelle = ArbeitsBlatt.PivotTables(PTabelle)
    With PivotTabelle.PivotCache
        .MissingItemsLimit = xlMissingItemsNone
        .Refresh
    End With
If PFeldOrientation = "xlDataField" Then
    Set PivotFeld = PivotTabelle.PivotFields(PFeld)
        With PivotFeld
            .Orientation = PFeldOrientation '<-- PivotFeldOrientation benötigt
        End With
    Else
    Set PivotFeld = PivotTabelle.PivotFields(PFeld)
    With PivotFeld
        .Orientation = PFeldOrientation
        .Position = 1
        .EnableMultiplePageItems = True
        .AutoSort xlAscending, PivotFeld
    End With
End If
             
If RepWee1 <> "" Or RepWee2 <> "" Then
    For Each PivotElement In PivotFeld.PivotItems
        Select Case PivotElement
            Case RepWee1
                PivotElement.Visible = True
            Case RepWee2
                PivotElement.Visible = True
            Case RepWee3
                PivotElement.Visible = True
            Case RepWee4
                PivotElement.Visible = True
            Case RepWee5
                PivotElement.Visible = True
            Case Else
                PivotElement.Visible = False
        End Select
    Next
End If
End Sub

 

Hallo,

in Visual-Basic gibt es das Konzept der "benannten Konstanten". Darunter versteht man, dass man im Source-Code statt einer Zahlenkonstante oder statt einer Stringkonstante einen Namen verwendet, der dann vom Compiler beim Compilieren wieder durch die ursprüngliche Konstante ersetzt wird. Das hat den Vorteil, dass im Programmtext ein aussagekräftiger Name anstelle einer relativ nichtssagenden Zahl steht.
Da bei einzelnen Objekteigenschaften eine ganze Liste von solchen Konstanten verwendet werden kann, wird eine solche Konstantenliste als eigener "Enumerationstyp" definiert.
Die .Orientation-Eigenschaft des PivotField-Objektes darf mit Konstanten der XlPivotFieldOrientation-Enumeration befüllt werden, wie man aus der Excel-VBA-Hilfe entnehmen kann - und nicht wie Du es getan hast mit einer Stringkonstanten "xlRowField" und etwas oberhalb "xlDataField" und oben in der Parameterliste der Sub PivotSetzen als "PFeldOrientation As String".

Der 1. Änderungsschritt besteht darin, den Datentyp für "PFeldOrientation" in der Parameterliste der Sub PivotSetzen von "String" auf "XlPivotFieldOrientation" zu ändern.

Code:

Public Sub PivotSetzen(PBlatt As String, PTabelle As String, PFeld As String, [COLOR=#0000cd]PFeldOrientation As XlPivotFieldOrientation[/COLOR], _
    Optional RepWee1 As String, Optional RepWee2 As String, Optional RepWee3 As String, Optional RepWee4 As String, Optional RepWee5 As String)

Der 2. Änderungsschritt besteht darin, die Programmpassage If PFeldOrientation = "xlDataField" Then in der Sub PivotSetzen zu ändern auf

Code:

If PFeldOrientation = [COLOR=#0000cd]xlDataField[/COLOR] Then

Der 3. Änderungsschritt besteht darin, die aktuelle Parameterliste der Subaufrufe entsprechend zu ändern, nachdem die formale Parameterliste der Sub PivotSetzen geändert wurde:

Code:

PivotSetzen "Tabelle6", "PivotTable2", "Wert A", [COLOR=#0000cd][COLOR=#0000cd]xlDataField 
[/COLOR][/COLOR]PivotSetzen "Tabelle6", "PivotTable2", "WW", [COLOR=#0000cd][COLOR=#0000cd]xlRowField, [/COLOR][/COLOR]ReportingWeek1, ReportingWeek2, ReportingWeek3, ReportingWeek4, ReportingWeek5

Der 4. Änderungsschritt ist ein erheblicher Vereinfachungsschritt und hat mit der Konstantenproblematik nichts zu tun. Du hast am Anfang 5 Wertzuweisungen an die Variabeln "ReportingWeekX", die man mittels einer With-Klammer erheblich vereinfachen kann:

Code:

With Worksheets("Data For Analysis")
  ReportingWeek1 = .Range("A1") & "-" & .Range("A3")
  ReportingWeek2 = .Range("A1") & "-" & .Range("A4")
  ReportingWeek3 = .Range("A1") & "-" & .Range("A5")
  ReportingWeek4 = .Range("A1") & "-" & .Range("A6")
  ReportingWeek5 = .Range("A1") & "-" & .Range("A7")
End With

Alles in allem schaut der resultierende Code folgendermaßen aus:

Code:

Option Explicit
Sub bla()
Dim ReportingWeek1 As String, ReportingWeek2 As String
Dim ReportingWeek3 As String, ReportingWeek4 As String, ReportingWeek5 As String

With Worksheets("Data For Analysis")
  ReportingWeek1 = .Range("A1") & "-" & .Range("A3")
  ReportingWeek2 = .Range("A1") & "-" & .Range("A4")
  ReportingWeek3 = .Range("A1") & "-" & .Range("A5")
  ReportingWeek4 = .Range("A1") & "-" & .Range("A6")
  ReportingWeek5 = .Range("A1") & "-" & .Range("A7")
End With
PivotSetzen "Tabelle6", "PivotTable2", "Wert A", xlDataField '<-- PivotFeldOrientation definiert
PivotSetzen "Tabelle6", "PivotTable2", "WW", xlRowField, _
    ReportingWeek1, ReportingWeek2, ReportingWeek3, ReportingWeek4, ReportingWeek5
End Sub
Public Sub PivotSetzen(PBlatt As String, PTabelle As String, PFeld As String, PFeldOrientation As XlPivotFieldOrientation, _
    Optional RepWee1 As String, Optional RepWee2 As String, Optional RepWee3 As String, Optional RepWee4 As String, Optional RepWee5 As String)
Dim ArbeitsBlatt As Worksheet, PivotTabelle As PivotTable
Dim PivotFeld As PivotField, PivotElement As PivotItem
Set ArbeitsBlatt = ActiveWorkbook.Worksheets(PBlatt)
Set PivotTabelle = ArbeitsBlatt.PivotTables(PTabelle)
    With PivotTabelle.PivotCache
        .MissingItemsLimit = xlMissingItemsNone
        .Refresh
    End With
If PFeldOrientation = xlDataField Then
    Set PivotFeld = PivotTabelle.PivotFields(PFeld)
        With PivotFeld
            .Orientation = PFeldOrientation '<-- PivotFeldOrientation benötigt
        End With
    Else
    Set PivotFeld = PivotTabelle.PivotFields(PFeld)
    With PivotFeld
        .Orientation = PFeldOrientation
        .Position = 1
        .EnableMultiplePageItems = True
        .AutoSort xlAscending, PivotFeld
    End With
End If
             
If RepWee1 <> "" Or RepWee2 <> "" Then
    For Each PivotElement In PivotFeld.PivotItems
        Select Case PivotElement
            Case RepWee1
                PivotElement.Visible = True
            Case RepWee2
                PivotElement.Visible = True
            Case RepWee3
                PivotElement.Visible = True
            Case RepWee4
                PivotElement.Visible = True
            Case RepWee5
                PivotElement.Visible = True
            Case Else
                PivotElement.Visible = False
        End Select
    Next
End If
End Sub

 

Hallo Exl121150,

also erstmal vielen dank für deine Erklärung, also auf soetwas wäre ich nie gekommen.
Nun muss ich nur noch die Änderungen eintragen und ausprobieren. :)

Nochmals Vielen Dank für die Hilfe.
Schöne Grüße

P.S. gebe dann noch ein Feedback
Tauchmann

 

Hallo,

da bin ich wieder.
Was soll ich sagen: ES KLAPPT ALLES SUPER GUT und FEIN :).
Nun kann es weiter gehen.

Vielen Vielen Dank für deine Hilfe und ich konnte wieder sehr viel dazu lernen :)

Dann wünsche ich dir einen schönen Mittwoch Abend.
Nochmals vielen vielen Dank.
Tauchmann

 

Hallo Exl121150</STRONG>,

nun funktioniert alles wunderbar und nun kommt ein weiteres Problem auf.
Wenn ich das Makro hintereinander zweimal laufen lasse wird leider auch zweimal der "Wert A" gesetzt.
Da nun aber der "Wert A" nur einmal gesetzt werden soll, muss also das PivotFeld "Wert A" erst ausgeblendet werden bevor man es neu setzt.
Nun wird das Pivot Data Field immer als "Summe von Wert A" angezeigt und kann somit nicht mit dem eingegebenen Wert "Wert A"für das Pivot Feld gelöscht werden.
Hast du eine Idee wie man "Wert A" mit Orientation = Hidden verstecken kann?
Bei mir funktioniert das irgendwie nicht.

Das Makro ist unter "Test5" in der Entwicklungsumgebung zu finden

Schöne Grüße
Tauchmann

 

Hallo Tauchmann,

Wenn man von folgender PivotTable ausgeht, die von folgendem Makro produziert wurde:

Code:

Sub PivotAufbauen()
    Dim Ws As Worksheet, Qu As Worksheet
    Dim Pt As PivotTable
'-----
    With ActiveWorkbook
      Set Ws = .Worksheets("Tabelle6")
      Set Qu = .Worksheets("Data")
    End With
    'Erzeugen der PT "PivotTable3" in "Tabelle6!A25" mit Daten aus "Data!A1:D9"
    Set Pt = Ws.PivotTableWizard(SourceType:=xlDatabase, _
             SourceData:=Qu.Range("A1:D9"), _
             TableDestination:=Ws.Range("A25"), TableName:="PivotTable3")
'-----
    With Pt
      'Zeilenfeld "WW" in 1.Spalte
      With .PivotFields("WW")
           .Orientation = xlRowField
           .Position = 1
      End With
      'Datenfeld "SummeWertA" enthält die Summen der Spalte "Wert A"
      .AddDataField .PivotFields("Wert A"), Caption:="SummeWertA", Function:=xlSum
      'Zeilenfeld "Wert A" in 2.Spalte
      With .PivotFields("Wert A")
          .Orientation = xlRowField
          .Position = 2
      End With
      
    End With 'Pt
    
End Sub

dann kann man mit diesen "xlHidden"-Konstanten die PT-Felder ausblenden:

Code:

Sub PivotFelder_Ausblenden()
    Dim Ws As Worksheet, Pt As PivotTable
    
    Set Ws = Worksheets("Tabelle6")
    Set Pt = Ws.PivotTables("PivotTable3")
    With Pt
       .PivotFields("SummeWertA").Orientation = xlHidden
       .PivotFields("Wert A").Orientation = xlHidden
       .PivotFields("WW").Orientation = xlHidden
    End With
    
End Sub