Pivot-Tabellen per VBA aktualisieren

Forumthread: Pivot-Tabellen per VBA aktualisieren

02.04.2017 17:42:06

Tim

Hallo,
ich bin ziemlich neu bei VBA und hoffe, jemand hier kann mir weiterhelfen.
Bisher habe ich VBA noch nicht mit Pivot-Tabellen genutzt.
Nun möchte ich gerne per VBA 2 Pivot-Tabellen aktualisieren (Refresh) UND
den Filter für die Kalenderwoche auf die jeweils letzte volle Woche setzen.
Meine Fragen bzw. Probleme damit:
1) Ist hier in VBA die Zeile .ClearAllFilters überhaupt nötig oder kann ich auch direkt den Filter ändern ?
2) Kann ich vor dem Ändern des Filters prüfen, ob diese Kalenderwoche (z.B. die Zahl 13) in meiner Tabelle überhaupt (schon) enthalten ist ? Sonst filtere ich nach etwas, das gar nicht existiert.
3) Schreibe ich den Code dafür so richtig oder kann man hier noch etwas besser / kürzer formulieren ? Ich war z.B. nicht sicher, ob With innerhalb With Sinn macht.
Mein Code:

    Dim varLastSunday As Date, varWeekNumber As Integer
varLastSunday = Date - Weekday(Date, vbUseSystemDayOfWeek)
varWeekNumber = CLng(Format(varLastSunday, "ww"))
Application.ScreenUpdating = False
With Worksheets(4)
.Range("StartColumns").EntireColumn.Hidden = False
.PivotTables("PivotTable9").PivotCache.Refresh
.PivotTables("PivotTable9").PivotFields("WEEK_NUMBER").ClearAllFilters
.PivotTables("PivotTable9").PivotFields("WEEK_NUMBER").CurrentPage = varWeekNumber
.PivotTables("PivotTable10").PivotCache.Refresh
.PivotTables("PivotTable10").PivotFields("week").ClearAllFilters
.PivotTables("PivotTable10").PivotFields("week").CurrentPage = varWeekNumber
End With
Application.ScreenUpdating = True

Vielen Dank für jede Hilfe damit.
VG,
Tim

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Pivot-Tabellen per VBA aktualisieren

02.04.2017 23:19:35

fcs

Hallo Tim,
zu Frage 1:
"ClearAllFilters" für das Pivot-Feld ist in diesem Fall nicht erforderlich.
zu Frage 2:
leider können Pivot-Berichte unter VBA schwierig zu handhaben sein.
Man kann prüfen, ob der Wert für ein Seitenfeld vorhanden ist.
Damit das funktioniert muss unter den Pivottabellenberichts-Optionen im Register "Daten" die Option für "Anzahl der Pro Feld beizubehaltende Elemente" auf "keine" gesetzt werden!
Ich hab das hier mit ins Makro eingebaut.
Je nachdem, welche Daten in der Daten-Quelle vorhanden sind, bleibt das Seitenfeld auf dem alten Wert stehen oder wird auf "alle Werte" gesetzt.
Mit dem Optionalen Parameter "bolClearAllFilters" im Makro kannst du dafür sorgen, dass das Feld immer auf alle Werte gesetzt wird, wenn die Woche in den Daten fehlt.
zu Frage 3: With ... End With
Geschachtelte With ... End With sind fast immer sinnvoll, solange dabei der Bezug zum in der With-Zeile genannten Objekt stimmt. Man spart sich Screibarbeit und die Makros werden übersichtlicher. Leider schafft der Makro-Recorder es nicht immer diese konsequent so aufzuzeichnen, so dass VBA-Anfänger sich da etwas schwer tun Anpassungen zu machen.
Oft ist es auch sinnvoll entsprechende Objekt-Variablen mit geeigneten Typ zu zu deklarieren und diesen dann die entsprechenden Objekte zuzuweisen. Dann kann man über die Variable(n) auf die Objekte zugreifen. Es erleichtert auch das Programmieren.
LG
Franz

'getestet unter Office 2010 profesional - Excel 2010
Sub prcPivotTabelle_testen()
Dim varLastSunday As Date, varWeekNumber As Integer
Dim pvTab As PivotTable, pvField As PivotField
Dim strMsg As String
varLastSunday = Date - Weekday(Date, vbUseSystemDayOfWeek)
varWeekNumber = CLng(Format(varLastSunday, "ww"))
Application.ScreenUpdating = False
With Worksheets(4)
.Range("StartColumns").EntireColumn.Hidden = False
Set pvTab = .PivotTables("PivotTable9")
With pvTab
.PivotCache.MissingItemsLimit = xlMissingItemsNone 'wichtig, damit Prüfung _
funktioniert, ob Woche vorhanden - kann auch unter den Optionen der _
Pivottabelle im Register "Daten" eingestellt werden (beizubehalende Elemente)
.PivotCache.Refresh
.RefreshTable
Set pvField = .PivotFields("WEEK_NUMBER")
If fncCheckPageFieldItems(pvField, varWeekNumber, bolClearAllFilters:=False) Then
pvField.CurrentPage = varWeekNumber
End If
End With
Set pvTab = .PivotTables("PivotTable10")
With pvTab
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
.RefreshTable
Set pvField = .PivotFields("week")
If fncCheckPageFieldItems(pvField, varWeekNumber) Then
pvField.CurrentPage = varWeekNumber
End If
End With
End With
Application.ScreenUpdating = True
End Sub
Function fncCheckPageFieldItems(pvField As PivotField, varItem As Variant, _
Optional bolClearAllFilters As Boolean = False) As Boolean
Dim pvItem As PivotItem
For Each pvItem In pvField.PivotItems
If pvItem.Name = Trim(Str(varItem)) Then
fncCheckPageFieldItems = True
Exit For
End If
Next
If fncCheckPageFieldItems = False Then
With pvField
If bolClearAllFilters = True Then .ClearAllFilters
MsgBox "In """ & .Parent.Name & """ keine Daten zu """ _
& .Name & """ " & varItem, _
vbOKOnly + vbInformation, "Prüfung Page-Field-Items"
End With
End If
End Function

AW: Pivot-Tabellen per VBA aktualisieren

03.04.2017 10:46:17

Tim

Hallo Franz,
vielen Dank für die super-detaillierte Antwort !
Das ist spitze und mehr als als ich erwartet hätte.
Ist eine riesen Hilfe !
VG und einen schönen Tag,
Tim

AW: Pivot-Tabellen per VBA aktualisieren

03.04.2017 10:55:19

Tim

Hallo Franz,
vielen Dank für die super-detaillierte Antwort !
Das ist spitze und mehr als als ich erwartet hätte.
Ist eine riesen Hilfe !
VG und einen schönen Tag,
Tim

Beliebteste Forumthreads (12 Monate)

Pivot-Tabellen per VBA aktualisieren

Forumthread: Pivot-Tabellen per VBA aktualisieren

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Pivot-Tabellen per VBA aktualisieren

Forumthread: Pivot-Tabellen per VBA aktualisieren

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread