Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Error 1004 bei PivotCache-Zuordnung

Betrifft: Error 1004 bei PivotCache-Zuordnung von: Hendrik
Geschrieben am: 22.10.2014 16:20:18

Hallo Forengemeinde,

ich bin nach langer Testphase an einem Punkt angekommen, an dem ich euch um Unterstützung bitten muss.

Script-Hintergrund: Es gibt 2 Excel-Workbooks. Nennen wir sie mal Quelle und Ziel.
In der Quell-Datei wird eine große Tabelle mit Budgetdaten monatlich aktualisiert und als Monatsdatei gespeichert (zu Archivierungszwecken). In der Zieldatei befinden sich etwa 25 Sheets, die jeweils ein Projekt abdecken. Die Trennung erfolgt, da die Datei mit den Projektdaten als Wertekopie weiter versendet wird, ohne das die Quelldatei offengelegt werden muss.

Script Ziel: Bei allen Sheets der Quelle muss einmal im Monat die Datenquelle auf die neue Monatsdatei gezogen werden. Hierzu könnte man das in jedem Sheet manuell tun.

Herangehensweise: Ich versuche, es über die Pivotcaches zu lösen. Ich ändere bei einem Sheet manuell die Datenquelle (Sheet 2) und weise jeder Pivottabelle den Cache der ersten Pivottabelle in diesem Sheet zu. Das sieht so aus (Script habe ich aus einem Tutorial kopiert):

1 Sub ChangePivotCache()
2 Dim pt As PivotTable
3 Dim wks As Worksheet
4
5 For Each wks In ActiveWorkbook.Worksheets
6   For Each pt In wks.PivotTables
7           pt.CacheIndex = ThisWorkbook.Sheets(2).PivotTables(1).CacheIndex
8   Next pt
9 Next wks
10 End Sub



Das Script lief eine Zeit lang wunderbar, aber plötzlich kommt bei jedem Ausführen eine Fehlermeldung "Laufzeitfehler 1004 Anwendungs- oder objektdefinierter Fehler" in Zeile 7.
Interessanterweise funktioniert die ausführung, wenn ich statt Sheet 2 bspw. Nummer 3 wähle (noch mit dem alten Cache) und damit die eben vorgenommene manuelle Änderung überschreibe.

Ich hoffe die Beschreibung ist verständlich.

Grüße,
Hendrik

  

Betrifft: AW: Error 1004 bei PivotCache-Zuordnung von: fcs
Geschrieben am: 22.10.2014 17:17:13

Hallo Hendrik,

ich hab dein Makro mal getestet. Es scheint zu funktionieren. Ich würde es jedoch in der folgenden Form schreiben.
Damit es korrekt funktioniert musst du darauf achten, dass zum Monatsende jeweils die Datenquelle der 1. Pivot-Tab im 2. Tabellenregister angepasst wird, bevor das Makro gestartet wird.

Könnte es vorkommen, dass ein eingestellter Berichtsfeld-Wert in den Monatsdaten nicht mehr vorkommt, aber noch als Filterwert in einem der 25 Blätter eingestellt ist?

Gruß
Franz

Sub ChangePivotCache()
  Dim pt As PivotTable, ptMaster As PivotTable
  Dim wks As Worksheet
  'Master-Pivot setzen = 1. Pivot-Tab im 2. Tabellenregister in der die Datenquelle zum  _
Monatswechsel geändert wird
  Set ptMaster = ThisWorkbook.Sheets(2).PivotTables(1)
  For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
      If Not (wks.Name = ptMaster.Parent.Name And ptMaster.Name = pt.Name) Then
           pt.CacheIndex = ptMaster.CacheIndex
      End If
    Next pt
  Next wks
End Sub