Hintergrundabfragen in Excel VBA effektiv steuern
Schritt-für-Schritt-Anleitung
-
Verhindere die Hintergrundaktualisierung: Stelle sicher, dass die Eigenschaft BackgroundQuery für jede OLEDB-Verbindung auf False gesetzt ist. Dies kannst Du durch den folgenden Code erreichen:
For Each Conn In ActiveWorkbook.Connections
Conn.OLEDBConnection.BackgroundQuery = False
Conn.OLEDBConnection.Refresh
Next Conn
-
Warte auf die Aktualisierung: Verwende die Application.Wait-Methode, um dem System Zeit zu geben, die Aktualisierungen abzuschließen. Beispiel:
Application.Wait Now + TimeSerial(0, 0, 1) ' Warte 1 Sekunde
-
Aktualisiere das Arbeitsblatt: Nach der Aktualisierung der Verbindungen kannst Du das Arbeitsblatt erneut berechnen:
Calculate
MsgBox ("Update was successful!")
-
Kombiniere alle Schritte: Der vollständige Code könnte so aussehen:
For Each Conn In ActiveWorkbook.Connections
Conn.OLEDBConnection.BackgroundQuery = False
Conn.OLEDBConnection.Refresh
Application.Wait Now + TimeSerial(0, 0, 1) ' Warte 1 Sekunde
Next Conn
Calculate
MsgBox ("Update was successful!")
Häufige Fehler und Lösungen
-
Laufzeitfehler 1004: Dieser Fehler tritt häufig auf, wenn Excel versucht, Daten zu aktualisieren, während eine andere Aktualisierung noch läuft. Löse dieses Problem, indem Du sicherstellst, dass BackgroundQuery auf False gesetzt ist, bevor Du die Aktualisierung startest.
-
Aktivierung der Hintergrundaktualisierung ausgegraut: Dies kann vorkommen, wenn die Datenquelle oder das Datenmodell in Excel nicht die richtigen Eigenschaften zulässt. Überprüfe die Verbindungen im Datenmodell und stelle sicher, dass Du nicht versuchst, BackgroundQuery für nicht unterstützte Verbindungen zu ändern.
Alternative Methoden
-
Dynamisches Warten: Anstelle einer festen Wartezeit kannst Du eine Schleife verwenden, um zu überprüfen, ob die Aktualisierung abgeschlossen ist, bevor Du fortfährst. Dies kann durch eine Kombination aus Do While-Schleifen und der Wait-Funktion geschehen.
Dim isUpdating As Boolean
isUpdating = True
Do While isUpdating
isUpdating = False
For Each Conn In ActiveWorkbook.Connections
If Conn.OLEDBConnection.BackgroundQuery = True Then
isUpdating = True
End If
Next Conn
Application.Wait Now + TimeSerial(0, 0, 1) ' Warte 1 Sekunde
Loop
Praktische Beispiele
Ein einfaches Beispiel zur Verwendung von ActiveWorkbook.RefreshAll könnte wie folgt aussehen:
Sub RefreshAllData()
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeSerial(0, 0, 5) ' Warte 5 Sekunden
MsgBox ("Alle Daten wurden aktualisiert!")
End Sub
Wenn Du mit mehreren Verbindungen arbeitest, kannst Du den oben beschriebenen Code mit NextConn verwenden, um sicherzustellen, dass alles nacheinander aktualisiert wird.
Tipps für Profis
-
Verwende DoEvents: Wenn Du möchtest, dass Excel während der Aktualisierung weiterhin reagiert, füge DoEvents in Deinen Code ein. Dies ermöglicht es Excel, andere Aufgaben zu erledigen, während es auf die Aktualisierung wartet.
-
Überwache den Status: Nutze die Application.StatusBar, um den Fortschritt der Aktualisierung anzuzeigen. Dies verbessert die Benutzererfahrung, indem Du den Benutzern Rückmeldungen über den Aktualisierungsstatus gibst.
FAQ: Häufige Fragen
1. Wie kann ich sicherstellen, dass alle Verbindungen aktualisiert werden?
Verwende die Schleife mit Conn.OLEDBConnection.Refresh, um jede Verbindung nacheinander zu aktualisieren.
2. Was mache ich, wenn BackgroundQuery ausgegraut ist?
Dies kann an den Verbindungseigenschaften liegen; stelle sicher, dass alle deine Verbindungen korrekt konfiguriert sind und dass Du die Berechtigungen für das Datenmodell überprüfst.
3. Kann ich die Wartezeit dynamisch gestalten?
Ja, indem Du eine Schleife verwendest, um den Status der Aktualisierungen zu überprüfen, kannst Du die Wartezeit anpassen, basierend auf der Anzahl der Verbindungen oder der Größe der Datenquelle.
