Die Excel-Funktion, auf die ich ein Jahr gewartet habe, ist immer noch nicht angekommen, also habe ich sie selbst erstellt

Indeks
  1. Mein VBA-Makro fügt automatisches PivotTable-Aktualisieren zu Excel hinzu
    1. Ein Ein-Klick-Umschalter hält Berichte aktuell
  2. Ich machte das Makro erinnern, welche Arbeitsmappe aktualisiert werden soll
    1. Automatische Updates unter meiner Kontrolle behalten
  3. Ein Timer lässt Excel PivotTables automatisch aktualisieren
    1. Manuelle Aktionen in einen geplanten Task umwandeln
  4. Das Makro sagt mir, wenn es läuft
    1. Feedback hinzufügen, ohne Excels Workflow zu ändern
  5. Das testen des makros enthüllte, wie Excel automatische aktualisierungen handhabt
    1. Was ich aus den Tests in echten Arbeitsmappen gelernt habe
    2. Excel nach Ihren Bedürfnissen gestalten
    3. VBA-Makro im Vollformat
  6. PivotTables aktiv.
  7. Live PivotTables deaktivieren
  8. Live PivotTables still deaktivieren
  9. Geplante Aktualisierung ausführen
  10. PivotTables aktualisieren
  11. Nächste Aktualisierung planen
  12. Nächste Aktualisierung abbrechen
  13. 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

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Go up