Die Excel-Funktion, auf die ich ein Jahr gewartet habe, ist immer noch nicht angekommen, also habe ich sie selbst erstellt
- Mein VBA-Makro fügt automatisches PivotTable-Aktualisieren zu Excel hinzu
- Ich machte das Makro erinnern, welche Arbeitsmappe aktualisiert werden soll
- Ein Timer lässt Excel PivotTables automatisch aktualisieren
- Das Makro sagt mir, wenn es läuft
- Das testen des makros enthüllte, wie Excel automatische aktualisierungen handhabt
- PivotTables aktiv.
- Live PivotTables deaktivieren
- Live PivotTables still deaktivieren
- Geplante Aktualisierung ausführen
- PivotTables aktualisieren
- Nächste Aktualisierung planen
- Nächste Aktualisierung abbrechen
- Workbook existiert
Erfahren Sie, wie Sie mit einem selbstentwickelten VBA-Makro in Excel PivotTables automatisiert aktualisieren, um Ihre Berichte stets aktuell zu halten und Ihre Effizienz zu steigern.
Einer der einfachsten Wege, einen Excel-Bericht unzuverlässig zu machen, besteht darin, die PivotTables nicht zu aktualisieren. Microsoft hatte vor einem Jahr die Auto-Refresh-Funktion angekündigt, aber in meiner Excel-Version ist sie noch nicht angekommen.
Ich habe das nicht hingenommen und eine eigene Lösung entwickelt: ein VBA-Makro in meinem persönlichen Makroarbeitsbuch. Jetzt habe ich einen Knopf in der Schnellzugriffsleiste (QAT), der PivotTables in jeder Arbeitsmappe automatisch aktualisiert. Ich kann das jederzeit deaktivieren. Während der Entwicklung baute ich ein paar Anpassungen ein, die besser zu meinem Workflow passen.
Der vollständige VBA-Code ist am Ende des Artikels. Um ihn zu verwenden, fügen Sie ihn in ein Modul von PERSONAL.XLSB ein, weisen Sie ToggleLivePivotTables einer QAT-Schaltfläche zu und klicken Sie darauf, während die zu überwachende Arbeitsmappe aktiv ist.
Mein VBA-Makro fügt automatisches PivotTable-Aktualisieren zu Excel hinzu
Ein Ein-Klick-Umschalter hält Berichte aktuell
Die Auto-Refresh-Funktion von Microsoft verfolgt einen datensatzbasierten Ansatz. Sie wird standardmäßig für neue PivotTables aktiviert und steuert alle PivotTables mit derselben Datenquelle. Ich wollte mehr Kontrolle: einen Schalter, den ich für eine bestimmte Arbeitsmappe aktivieren kann, damit jede PivotTable dieser Arbeitsmappe automatisch aktualisiert wird.
Mein Tool funktioniert einfach:
- Ein Klick auf die Schaltfläche in der QAT aktiviert die Live-PivotTables.
- Nachdem die Funktion aktiviert ist, werden die PivotTables sofort aktualisiert.
- Sie aktualisieren sich im gewählten Intervall.
- Ein erneuter Klick deaktiviert die Funktion.
Ich nannte es "Live PivotTables", weil es funktioniert wie ein Schalter: aktivieren für automatische Updates, deaktivieren, wenn nicht mehr nötig. Bei Aktivierung erscheint eine Bestätigungsnachricht mit der überwachten Arbeitsmappe und dem Intervall. Das hilft, wenn mehrere Excel-Dateien geöffnet sind und ich wissen möchte, welche überwacht wird.
Ich habe den Fokus bewusst eng gehalten. Das Makro aktualisiert nur PivotTables und ersetzt nicht den Excel-Befehl "Alle aktualisieren". Es bleibt unberührt von Power Query-Aktualisierungen oder externen Verbindungen.
Ich machte das Makro erinnern, welche Arbeitsmappe aktualisiert werden soll
Automatische Updates unter meiner Kontrolle behalten
Eine der ersten Designentscheidungen betraf die Situation mit mehreren offenen Arbeitsmappen. Ich wollte eine bewusste Aktualisierung: Wenn ich klicke, wähle ich gezielt die Arbeitsmappe aus, die überwacht werden soll.
Das Makro speichert den Namen der aktiven Arbeitsmappe:
Public mWorkbookName As String
Bei Aktivierung von Live PivotTables wird der aktuelle Arbeitsmappenname aufgezeichnet:
mWorkbookName = wb.Name
Von da an erfolgen die geplanten Aktualisierungen nur für diese Arbeitsmappe. Der gleiche Name wird verwendet, wenn Live PivotTables deaktiviert sind, sodass ich immer informierte über die betroffene Arbeitsmappe bin.
Zusätzlich implementierte ich eine Sicherheitsmaßnahme. Wenn die überwachte Arbeitsmappe geschlossen wird, deaktiviert sich das Makro automatisch.
weiterhin auf eine nicht mehr existierende Arbeitsmappe zu verweisen.
Ein Timer lässt Excel PivotTables automatisch aktualisieren
Manuelle Aktionen in einen geplanten Task umwandeln
Der nächste Schritt: wiederholte Aktualisierung ohne Mausklick. Hierfür nutzte ich Excel’s integrierten VBA-Planer, Application.OnTime. Zuerst setzte ich die Steuerung für die Aktualisierungsfrequenz:
Public Const REFRESH_INTERVAL_SECONDS As Long = 300
Standardmäßig auf fünf Minuten (300 Sekunden) eingestellt, ließ ich es während der Tests auf 15 Sekunden herab. So konnte ich schnell testen, ob die automatische Aktualisierung funktionierte.
Das Makro plant nun die nächste Aktualisierung:
Application.OnTime _ EarliestTime:=mNextRun, _ Procedure:="RunScheduledRefresh"
Ist der geplante Zeitpunkt erreicht, führt Excel den Aktualisierungsprozess erneut aus. Wichtig: Der nächste Timer wird erst nach Abschluss der vorherigen Aktualisierung festgelegt. In Tests mit einer komplexen Arbeitsmappe, die ein Datenmodell und mehrere PivotTables enthielt, dauerten die Aktualisierungen länger als in einfachen Testarbeiten. Doch das Makro wartete brav, anstatt mehrere Überlappungen zu erzeugen. Das sorgt für Vorhersehbarkeit, selbst wenn Updates mal länger dauern.
Das Makro sagt mir, wenn es läuft
Feedback hinzufügen, ohne Excels Workflow zu ändern
Automatisierte Aufgaben brauchen Signalsysteme. Live PivotTables bietet zwei einfache Feedback-Methoden: eine Bestätigungsnachricht beim Aktivieren und eine temporäre Statusleistenaktualisierung während der Aktualisierung.
Aktiviere ich Live PivotTables, bestätigt das Makro die überwachte Arbeitsmappe und die Aktualisierungsrate:
MsgBox "Live PivotTables ist jetzt AKTIV." & vbCrLf & vbCrLf & _ mWorkbookName & " wird alle " & _ REFRESH_INTERVAL_SECONDS & " Sekunden aktualisiert.", _ vbInformation
Beim Start der Aktualisierung ändert sich die Statusleiste:
Application.StatusBar = "Live PivotTables: Aktualisiere..."
Die Nachricht bleibt sichtbar, während Excel aktualisiert. Selbst schnelle Aktualisierungen löschen sie nicht sofort. Nach zwei Sekunden entfernt das Makro die Statusleiste und stellt die Anzeige wieder auf den Normalzustand zurück.
Das testen des makros enthüllte, wie Excel automatische aktualisierungen handhabt
Was ich aus den Tests in echten Arbeitsmappen gelernt habe
Der Bau dieser Funktion erinnerte mich daran, dass automatische Excel-Aktualisierungen nicht unsichtbar ablaufen. Hier einige Erkenntnisse aus den Tests:
- Aktualisierungszeit hängt von der Arbeitsmappe ab: Datenmodell- und große Datensätze benötigen längere Zeit. Jede PivotTable wurde korrekt aktualisiert.
- Aktualisierungen schränken kurz ein: Excel reagiert zeitweise nicht und zeigt den drehenden Cursor während der Aktualisierung.
- Aktualisierungen beenden den Kopiermodus: Wurdest du beim Kopieren unterbrochen, bricht Excel die Auswahl ab.
- Das Bearbeiten einer...
Zelle verzögert Aktualisierung: Tippen Sie, wenn die neue Aktualisierung fällig ist? Excel wartet, bis Sie mit dem Bearbeiten fertig sind, bevor es ausführt.
- Rückgängig macht keine Änderungen an Quelldaten: Nach einer Aktualisierung hat Ctrl+Z keinen Einfluss auf die Quelldaten. Die Aktualisierung erfolgt separat.
Excel nach Ihren Bedürfnissen gestalten
Live PivotTables zeigen, dass die bedeutendsten Excel-Verbesserungen oft die sind, die Sie selbst implementieren. Mit einer persönlichen Makroarbeitsmappe können Sie eine maßgeschneiderte Excel-Toolbar mit VBA erstellen. Fügen Sie einfache Befehle wie die Einfügung eines Zeitstempels oder komplexere Funktionen wie klickbare Tabellenverzeichnisse hinzu. Ein wenig VBA verwandelt Excel in ein individuelleres Werkzeug, das nach Ihren Vorstellungen funktioniert.
VBA-Makro im Vollformat
Option Explicit Public Const REFRESH_INTERVAL_SECONDS As Long = 300 Public mIsEnabled As Boolean Public mNextRun As Date Public mWorkbookName As String Public Sub ToggleLivePivotTables() If mIsEnabled Then If Not WorkbookExists(mWorkbookName) Then DisableLivePivotTablesSilently EnableLivePivotTables Else DisableLivePivotTables End If Else EnableLivePivotTables End If End Sub Public Sub EnableLivePivotTables() Dim wb As Workbook Set wb = ActiveWorkbook If wb Is Nothing Then MsgBox "Keine Arbeitsmappe ist geöffnet.", vbExclamation Exit Sub End If If mIsEnabled Then MsgBox "Live PivotTables ist bereits aktiviert für " & _ mWorkbookName & ".", _ vbInformation Exit Sub End If mWorkbookName = wb.Name mIsEnabled = True If Not RefreshPivotTables Then DisableLivePivotTables MsgBox "Live PivotTables fehlgeschlagen. Die Funktion wurde deaktiviert.", _ vbCritical Exit Sub End If ScheduleNextRefresh MsgBox "Live.
PivotTables aktiv.
aktualisiert sich alle Sekunden.
Live PivotTables deaktivieren
CancelNextRefresh
mIsEnabled = False
Wenn <> "" Dann
MsgBox "Live PivotTables wurde für deaktiviert.", vbInformation
End Wenn
mWorkbookName = ""
Application.StatusBar = False
Live PivotTables still deaktivieren
CancelNextRefresh
mIsEnabled = False
mWorkbookName = ""
Application.StatusBar = False
Geplante Aktualisierung ausführen
Wenn Nicht mIsEnabled Dann
Exit Sub
Wenn Nicht WorkbookExists(mWorkbookName) Dann
DisableLivePivotTablesSilently
Exit Sub
End Wenn
Wenn Nicht RefreshPivotTables Dann
CancelNextRefresh
mIsEnabled = False
MsgBox "Live PivotTables fehlgeschlagen. Die Funktion wurde deaktiviert.", vbCritical
mWorkbookName = ""
Exit Sub
End Wenn
ScheduleNextRefresh
PivotTables aktualisieren
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Dim previousScreenUpdating As Boolean
Dim waitUntil As Date
On Error GoTo Failed
Set wb = Workbooks(mWorkbookName)
previousScreenUpdating = Application.ScreenUpdating
Application.StatusBar = "Live PivotTables: Aktualisieren..."
DoEvents
Application.ScreenUpdating = False
Für Jeden ws In wb.Worksheets
Für Jeden pt In ws.PivotTables
pt.RefreshTable
Nächstes pt
Nächstes ws
Application.ScreenUpdating = previousScreenUpdating
waitUntil = Now + TimeValue("00:00:02")
Do While Now < waitUntil
DoEvents
Loop
Application.StatusBar = False
RefreshPivotTables = True
Exit Function
Failed:
Application.ScreenUpdating = previousScreenUpdating
Application.StatusBar = False
RefreshPivotTables = False
End Function
Nächste Aktualisierung planen
CancelNextRefresh
mNextRun = Now + TimeSerial(0, 0, REFRESH_INTERVAL_SECONDS)
Application.OnTime
EarliestTime:=mNextRun,
Procedure:="RunScheduledRefresh"
Nächste Aktualisierung abbrechen
On Error Resume Next
Wenn mNextRun <> 0 Dann
Application.OnTime
EarliestTime:=mNextRun,
Procedure:="RunScheduledRefresh",
Schedule:=False
End Wenn
On Error GoTo 0
mNextRun = 0
Workbook existiert
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(workbookName)
WorkbookExists = Not wb Is Nothing
On Error GoTo 0
Bist du neugierig auf weitere Artikel wie Die Excel-Funktion, auf die ich ein Jahr gewartet habe, ist immer noch nicht angekommen, also habe ich sie selbst erstellt? Entdecke spannende Inhalte in der Kategorie Wie man.

Schreibe einen Kommentar